CODEWITHSUNDEEP
vbaexcel
user3125707
user3125707

Reputation: 409

Confirmation to Save before Closing Workbook

I came across some vba code online which forces users to enable macros. It displays only "Sheet1" when macros are turned off and when they are turned on it hides "Sheet1" and unhides the working sheets. This is a very ingenious code but there is one drawback. When I close the file it doesn't ask me if I want to save the file or not. It automatically saves itself so as to keep the rest of the sheets hidden. I would like to know if there is a way to get around this, that is if I could enable the "save/ close without saving" prompt and also keep the sheets hidden except for "Sheet1"

Below is the code:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Declare your variables
Dim ws As Worksheet

'Step 2: Unhide the Starting Sheet
Sheets("Sheet1").Visible = xlSheetVisible

'Step 3: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets

'Step 4: Check each worksheet name
If ws.Name <> "Sheet1" 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

End Sub


Private Sub Workbook_Open()

'Step 1: Declare your variables
Dim ws As Worksheet

'Step 2: Start looping through all worksheets
For Each ws In ThisWorkbook.Worksheets

'Step 3: Unhide All Worksheets
ws.Visible = xlSheetVisible

'Step 4: Loop to next worksheet
Next ws


'Step 5: Hide the Start Sheet
Sheets("Sheet1").Visible = xlVeryHidden

End Sub

Upvotes: 0

Views: 2762

Answers (1)

Gareth
Gareth

Reputation: 5243

You can wrap ActiveWorkbook.Save with your own message like so:

If MsgBox("Would you like to save?", vbYesNo + vbQuestion, "Save Workbook") = vbYes Then
    ActiveWorkbook.Save
End If

Upvotes: 1

Related Questions