Macro to unhide sheets when macros has been enabled.

[CV899000]

Hi,

I have some problems with my workbook.

I have a workbook that I am going to send to employees and it contains macros.
Now, if those employees does not know excel, they might disable macros since it states that it is a security threat. However, then all my work with the workbook is wasted because they cannot use it.

I have therefore created a sheet called "macro disabled" that is showing when they open excel. If they enable macros, I have made a vba code that hides the "macro disabled" sheet and shows three other sheets.

When I close the workbook, I again have a macro that hides the three sheets and unhides the "macro disabled" sheet so that it again is ready if somebody doesn't enable macros.

I then wanted excel to stop asking if the users wanted to save everytime they closed the workbook, and I can do that also, but when I do that, then it doesn't hide the three sheets and shows the "macro disabled" sheet... Please help and see my code beneath.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Macros disabled").Visible = True
Sheets("Information").Visible = xlVeryHidden
Sheets("Prices").Visible = xlVeryHidden
Sheets("Copied Prices").Visible = xlVeryHidden
For Each Worksheet In Worksheets
If Worksheet.Name <> "Macros disabled" Then Worksheet.Visible = xlSheetVeryHidden
Next
Me.Saved = True
End Sub

 

[CV899000]

I cannot get the code to remain in the document. Everytime I close the document, the code disappears and I have to manually redo all the steps, again and again..

 

[CV899000]

Okay guys, I got it to work everytime I close the workbook now.

I only had to use this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Saved = True
End Sub

 

[Jaafar Tribak]

Before your first save ie (before saving the code), you will need to temporarly disable the application events.

Write this in the Immediate window and press Enter:

application.EnableEvents=False

Now, save your workbook and then Enable back the application events or just close the entire Excel application .

Next time the workbook is open, the code will have taken effect.