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