Hi there fellow Excel users,
I'm pretty new to VBA (but studied C++ and other languages).
I've come accross a problem with a BeforeSave procedure not executing properly when fired by Me.Save as part of a BeforeClose procedure.
Consider the piece of code below.
When manually saving (through Ctrl+S), the Workbook_BeforeSave event is fired and everything works as expected.
However, firing the BeforeClose event does not bring the expected result. The "Me.Save" line in the BeforeClose effectively fires the Workbook_BeforeSave event, as confirmed by the MsgBox that pop ups, but the two ThisWorkbook.Worksheets().Visible lines are not getting executed.
I don't quite understand why it's acting like this, as the code seem pretty simple and logical to me. Is there some special restriction that I wouldn't know about event procedures that would prevent this piece of code from working properly?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Msg As String
Dim Ans As String
If Me.Saved = False Then
Msg = "Do you want to save the changes you made to " & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
ClosingWorkbookFlag = 1
Me.Save
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
Me.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
CurrentSheetName = ActiveSheet.Name
MsgBox "Workbook Before Save start" 'for debug
ThisWorkbook.Worksheets("MacrosRequired").Visible = True
ThisWorkbook.Worksheets("Main").Visible = False
If ClosingWorkbookFlag = 0 Then
Application.OnTime Now, "AfterSave"
End If
End Sub
Bookmarks