Stop BeforeCloseEvent when workbook close

Question

I have this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Sheets("MACROS").Visible = True
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "MACROS" Then
            ws.Visible = xlVeryHidden
        End If
    Next ws
    Application.CommandBars("Ply").Enabled = True
End Sub

The code displays the MACROS sheet when macros are disabled. The thing is that when macros are enabled, and some work had been done on the workbook, and the book is closed by clicking the "X" (Close Button), it prompts to save but displays the MACROS sheet.

I am looking to have the program remain on active sheet while displaying save prompt.

Would someone be so kind to please help me with modifying the above code? All and any help will be greatly appreciated!

Hi, no that line is important as the MACROS sheet must display in the event of opening the workbook with MACROS disabled. I would like just if the workbook is closed with MACROS enabled, it must stay on active sheet?... — juriemagic
@juriemagic explain what you mean with macros being enabled or disabled? If you disable macros from running how can this code ever work. — DragonSamu
The beforeclose event runs regardless of whether macros is enabled or disabled. Now, this I got from the people who helped me with this code. (Unless of course I misunderstood). And it works!..if I disable macros and run the book, the MACROS sheet shows up, prompting to please enable macros...I don't know how it works,but it does? — juriemagic
Hang on...I did some digging...The above is not true, I apologise.(I'm a bit gullible when it comes to VBA)..I have realized it's because the other sheets have their visible attribute set to veryhidden, all but the MACROS sheet, so when macros is disabled, it automatically displays the macros sheet...I do apologise for this misunderstanding...( I have learned from this), so yes,removing the line which makes sheet macros visible, is the answer...Thank you very much.. — juriemagic

psychicebola psychicebola · Accepted Answer · 2015-08-27T13:46:36

Remove the line Sheets("MACROS").Visible = True.

The code should be:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "MACROS" Then
            ws.Visible = xlVeryHidden
        End If
    Next ws
    Application.CommandBars("Ply").Enabled = True
End Sub

Stop BeforeCloseEvent when workbook close

1 Answers