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