Endless looping with an aftersave subroutine

Question

I am trying to maintain the security of my workbook using veryhidden to force users to enable macros.

This works great except that users have complained about the lack of a dialog box to ask if they wish to save or not on close.

I have added the save/notsave functionality in the form of a msgbox, however this has opened a specific case where security is breached:

Namely, if the user opens the file, enables macros, gets past the splash screen, saves the workbook with the save function, then closes without saving, then the next time the workbook is opened it will not force the user to enable macros.

I have tried to get around this with the following code set to run at the time of saving (i didnt have luck with before save because it was saving again after the code had run, which defeated the point of the code) but this code seems to generate an endless loop triggered by the save. I tried placing a check at the beginning to check if the state was saved, but since the macro runs after the save this is always a true state for the macro.

Similarly i tried adding a check at the bottom to see if its saved and if it did, then exit the subroutine, however the code never gets this far because it triggers again when it saves.

Anyone have any ideas? (code below)

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

If Success = True Then
    'Step 1: Define WS variable
        Dim ws As Worksheet
    'Step 2: Unhide the Starting Sheet
        Sheets("START").Visible = xlSheetVisible
    'Step 3: Start looping through all worksheets
        For Each ws In ThisWorkbook.Worksheets
    'Step 4: Check each worksheet name
        If ws.Name <> "Start" Then
    'Step 5: Hide the sheet
        ws.Visible = xlVeryHidden
        End If
    'Step 6: Loop to next worksheet
        Next ws
    'Step 7: Save the workbook
        ActiveWorkbook.Save
    'Step 8: Start looping through all worksheets
        For Each ws In ThisWorkbook.Worksheets
    'Step 9: Re-Unhide All Worksheets
        ws.Visible = xlSheetVisible
    'Step 10: Loop to next worksheet
        Next ws
    'Step 11: Re-Hide the Start Sheet
        Sheets("START").Visible = xlVeryHidden
    'Step 12: Exit loop
        If ThisWorkbook.Saved = True Then
        Exit Sub
        End If
End If
End Sub

Answer 1

A slightly different strategy:

1. Workbook_BeforeSave to unhide START sheet and hide all the others which will fire
2. Workbook_AfterSave to hide START and unhide all the other sheets and
3. Workbook_Open to hide START and unhide all the other sheets.

The code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet

    Application.ScreenUpdating = False 'stops flicker and improves speed
    Sheets("START").Visible = xlSheetVisible
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Start" Then
            ws.Visible = xlVeryHidden
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim ws As Worksheet

    Application.ScreenUpdating = False 'stops flicker and improves speed
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
    Sheets("START").Visible = xlVeryHidden
    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
    Dim ws As Worksheet

    Application.ScreenUpdating = False 'stops flicker and improves speed
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
    Sheets("START").Visible = xlVeryHidden
    Application.ScreenUpdating = True
End Sub

Answer 2

to avoid endless triggering loop:

    'Step 7: Save the workbook
        Application.EnableEvents = False
        ActiveWorkbook.Save
        Application.EnableEvents = True