Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Asking for Workbook_BeforeClose clarification XL 97

  Asked By: James    Date: Nov 12    Category: MS Office    Views: 977
  

It sure is neat to see all my work come together after
several years. I'm doing serial I/O, saving/loading files(duplicating
a manufacturer's special format) and just added a menu, up top, to run
some of this. Removing the menu, cleanly upon Workbook close, is a
bit tricky. What MSDN has looks sufficient, but a couple of things
puzzle me:


For BeforeClose, MSDN (copied fully below w/ my added comments)
shows two things I don't understand:

1- A declaration of:
Public Event BeforeClose As WorkbookEvents_BeforeCloseEventHandler

Must I do this declaration if I already have a functional
BeforeClose SUB?
I thought perhaps this sets up things so the BeforeClose is
enabled, but I already have that. What I put in that sub, does
execute at Close. The BeforeClose was an included Procedure
(pull-down in the upper right of the Editor and the Object Browser),
so I just used it.
Perhaps it is needed to allow me to do what could be a protected
property i.e. "Me.Saved = True" ??


2- Handles Me.BeforeClose

There were too many hits on "handles" to sort through in the MSDN
search to figure this one out....ok, tending toward lazy (;-)

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Ethan Bouchard     Answered On: Nov 12

A few hours of work  & I got it converted to the correct VB and working
just right.

 
Answer #2    Answered By: Rose Hughes     Answered On: Nov 12

could you post your code so we may learn from it?

 
Answer #3    Answered By: Jessica Brown     Answered On: Nov 12

Of course. Here's my finished BeforeClose event  Handler.
Background:
I went to MSDN (searched) and copied  the BeforeClose code I found
there, knowing full well it was for .NET rather than VBA, but also
knowing there are close  similarities (from previous searches).
I could see *what* it was trying to do and made modifications I
needed and some I could see were necessary. Then, I ran it and
corrected each error that poped up - they were relativaly easy syntax
differences. Here's the final code. I'll add some clarifying
comments as I see it here on the screen. I hope Yahoo doesn't
wrap/chop the lines. If it does, I'll try to repost and compensate.

Specific Requirement:
I set up (in the Workbook_Open Sub) an additional menu  on the main
Workbook Menu and wanted to remove it when quitting, but, of course,
not if the Close was Cancelled.

Enjoy:
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Private Sub Workbook_BeforeClose(Cancel As Boolean) ' _
' Handles Me.BeforeClose ' didn't know what this does, so I
commented it out

Dim MB As Object ' to shorten some lines.
Dim result As Integer ", for some reason, had trouble making the
' MsgBox functioning as shown in the example,
' so used this to capture the MsgBox clicked button.

Debug.Print "Preemptive Save Dialog" ' Immediate Window status report

' Looking now, not sure why I needed  the following. I don't appear
to change any cells, so shouldn't need to inhibit Change events.
Application.EnableEvents = False

If Not Me.Saved Then
Debug.Print " Not Saved, so..."; ' Note the ";" for Immediate
window reading pleasure (for fine tuning the functionality).
result = MsgBox("Do you want to save the " & _
"changes you made to " & Me.Name & " ?" &
Chr(13) & Chr(13) & _
Chr(9) & " Save it before putting this
puppy away ?", _
3 * 16 + 3, "Example")

Select Case result
Case 2 ' Cancel - Don't Save and don't Close
Debug.Print " nothing. Return"
Cancel = True ' Parameter sent back TO VBA upon Sub exit.
Application.EnableEvents = True
Exit Sub

Case 6 ' Yes - Do Close cleanup (below), Save & Close
Debug.Print " Save then Close."
Application.EnableEvents = True
Me.Save

' The following code inhibits the default Save File dialog by
saying it is saved.
Case 7 'No - Don't Save, Do Close cleanup& Close.
Debug.Print " Just close."
Me.Saved = True

End Select

End If

Debug.Print " Killing menu" ' that I added  in the Workbook_Open Sub.
' I changed the order of the cases (from MSDN) so both cases
' that needed the Menu removal could use the same code.
Set MB = MenuBars("Worksheet")
MB.Menus(" ").Delete ' Removes the top  level menus
called " ".
MB.Menus("&TH-F6A").Delete ' and called "Kenwood".
Application.EnableEvents = True
End Sub
'------- NOTE ------
' I placed a 'blank' Menu ahead of my "TH-F6A" Menu
' so my menu stood out more (:-)

 
Answer #4    Answered By: Tia Hughes     Answered On: Nov 12

I tried to compensate for most line wraps in my reader.


Of course. Here's my finished BeforeClose event  Handler.
Background:
I went to MSDN (searched) and copied  the BeforeClose code I found
there, knowing full well it was for .NET rather than VBA, but also
knowing there are close  similarities (from previous searches).
I could see *what* it was trying to do and made modifications I
needed and some I could see were necessary. Then, I ran it and
corrected each error that poped up - they were relativaly easy syntax
differences. Here's the final code. I'll add some clarifying
comments as I see it here on the screen. I hope Yahoo doesn't
wrap/chop the lines. If it does, I'll try to repost and compensate.

Specific Requirement:
I set up (in the Workbook_Open Sub) an additional menu  on the main
Workbook Menu and wanted to remove it when quitting, but, of course,
not if the Close was Cancelled.

Enjoy:
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Private Sub Workbook_BeforeClose(Cancel As Boolean) ' _
' Handles Me.BeforeClose ' didn't know what this does,
' so I commented it out.

Dim MB As Object ' to shorten some lines.
Dim result As Integer ", for some reason, I had trouble
' making the MsgBox functioning as shown in the example,
' so used this to capture the MsgBox clicked button.

Debug.Print "Preemptive Save Dialog" ' Immediate Window status report

' Looking now, not sure why I needed  the following.
' I don't appear to change any cells, so shouldn't
' need to inhibit Change events.
Application.EnableEvents = False

If Not Me.Saved Then
Debug.Print " Not Saved, so..."; ' Note the ";" for Immediate
'window reading pleasure (for fine tuning the functionality).

' Sorry about this line's wrap. I use double
' CRs [chr$(13)] to improve readability. Also,
' I try to make it very clear which button to push.
result = MsgBox("Do you want to save the " & _
"changes you made to " & Me.Name & " ?" &
Chr(13) & Chr(13) & _
Chr(9) & " Save it before putting this
puppy away ?", _
3 * 16 + 3, "Example")


Select Case result
Case 2 ' Cancel - Don't Save and don't Close
Debug.Print " nothing. Return"
Cancel = True ' Parameter sent back TO VBA upon Sub exit.
Application.EnableEvents = True
Exit Sub

Case 6 ' Yes - Do Close-cleanup (below), Save & Close
Debug.Print " Save then Close."
Application.EnableEvents = True
Me.Save

' The following inhibits the default Save File dialog
' by faking that it is saved.
Case 7 'No - Don't Save, Do Close-cleanup & Close.
Debug.Print " Just close."
Me.Saved = True

End Select

End If

Debug.Print " Killing menu" ' that I added  in the Workbook_Open Sub.
' I changed the order of the cases (from MSDN) so both cases
' that needed the Menu removal could use this same code.

Set MB = MenuBars("Worksheet")
MB.Menus(" ").Delete ' Removes the top  level menus.
MB.Menus("&TH-F6A").Delete ' and called "&TH-F6A".
Application.EnableEvents = True
End Sub

 
Didn't find what you were looking for? Find more on Asking for Workbook_BeforeClose clarification XL 97 Or get search suggestion and latest updates.