Workbook_BeforeClose and save dialogue box

[JoJoRabbit]

This macro is mainly to hide the sheets of the workbook workbook to only leave behind a splash sheet so that users must enable macros for another macro that unhides necessary sheets and hides the splash sheet on workbook_open.

However, this macro saves the workbook without the usual dialogue box of " save" " dont save" "cancel" appearing which may put off some users, so i'm trying to include a message box that can either save, don't save , or go back to as the user was as if it was a normal save message box, but i am having some issues with this part highlighted in red

code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

x = MsgBox("Are you sure you want to quit? clicking 'yes' will save the changes made,clicking 'no' will exit without saving, "cancel" will return you as you were" , vbYesNoCancel)

    If x = vbYes Then

        ThisWorkbook.Save

   ElseIf x = vbNo Then

      Activeworkbook.close 

   ElseIf x = vbCancel Then

  "go back to original state 
     
    End If
   

  Dim ws As Worksheet

  Sheets("START").Visible = xlSheetVisible

  For Each ws In ThisWorkbook.Worksheets

  If ws.Name <> "START" Then

  ws.Visible = xlVeryHidden

  End If

  Next ws

  ActiveWorkbook.Save
End Sub

 

[sykes]

Hi, and welcome to Mr. Excel!

This should do what you're asking for.
NB Test on a COPY of your work first!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
x = MsgBox("Are you sure you want to quit? clicking 'yes' will save the changes made,clicking 'no' will exit without saving, 'cancel' will return you as you were", vbYesNoCancel)

    If x = vbCancel Then Exit Sub
    If x = vbYes Then GoTo Save_yes
    ActiveWorkbook.Close (False)

Save_yes:     Dim ws As Worksheet
    Sheets("START").Visible = xlSheetVisible    
    For Each ws In ThisWorkbook.Worksheets        
        If ws.Name <> "START" Then   
            ws.Visible = xlVeryHidden
        End If
    Next ws

ActiveWorkbook.Save
End Sub

 

[JoJoRabbit]

Hi sykes, thanks for the feedback. I will take a look at the xl2bb you mentioned

When i press cancel i still get prompted with a save dialogue box. And thereupon the message box no longer appears with this code when i close excel.

It seems there is no "No" option ? What happens if you don't assign a code to the no option haha im so new at this.

"yes" button works fine.



As of now im using this work around which does not allow close without saving, and to do so, the user must manually revert to a previous version and then close the file. which is pretty bad imo but its the only thing that worked



Private Sub Workbook_BeforeClose(Cancel As Boolean)
x = MsgBox(" If 'OK' is clicked, workbook will save upon closing.'Cancel' will return you as you were. To revert to previous version please go to file > info > version history", vbOKCancel, "Notice")
If x = vbOK Then
Dim ws As Worksheet
Sheets("START").Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "START" Then
ws.Visible = xlVeryHidden
End If
Next ws
ActiveWorkbook.Save
Application.Quit

ElseIf x = vbCancel Then
Cancel = True

End If

End Sub

 

[sykes]

Amended code for you.
Once again, test on a copy first!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
x = MsgBox("Are you sure you want to quit? clicking 'yes' will save the changes made,clicking 'no' will exit without saving, 'cancel' will return you as you were", vbYesNoCancel)

    If x = vbCancel Then Cancel = True: Exit Sub
    If x = vbYes Then GoTo Save_yes
    Me.Saved = True
    Exit Sub

Save_yes:     Dim ws As Worksheet
    Sheets("START").Visible = xlSheetVisible
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "START" Then
            ws.Visible = xlVeryHidden
        End If
    Next ws

ActiveWorkbook.Save
End Sub