PLEASE..URGENT..Shared Workbook DisEnabled Macros
Posted by Jim on February 03, 2001 1:06 PM
URGENT!! Need your EXPERT ASSISTANCE. When my user Disenables Macros, the WorkBook opens and all Sheets are viewable. How can I prevent this. I am in th2 12th hour of my project and really need urgent help. Thank you.
Posted by Dax on February 03, 2001 1:17 PM
How about hiding all your worksheets, protecting your workbook and using your macro to unprotect your workbook and unhide the sheets, e.g. in the WorkBookOpen event of your workbook.
Posted by Jim on February 03, 2001 1:41 PM
Dax,
What is the code for doing this... I understand the hiding. I have tried to hide all sheets but when you disenable the Macros...The Menu bar appears and allows you to unhide the sheets. So you are suggesting a little Protect Workbook routine for a shared Workbook...I will try it and get right back to you. Thank you.
Posted by Celia on February 03, 2001 5:39 PM
The following is from another forum (submitted by James Hoeffer). It hides all sheets if macros are disabled and shows all sheets if macros are enabled. Perhaps you can adjust it to fit your requirements :-
Insert a blank worksheet page into your workbooks and name it something like "Macros not enabled". Then add the following code :-
Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Macros not enabled").Visible = True
For Each ws In Worksheets
If ws.Name <> "Macros not enabled" Then ws.Visible = xlVeryHidden
Next
End Sub
Private Sub Workbook_Open()
For Each ws In Worksheets
If ws.Name <> "Macros Not Enabled" Then ws.Visible = True
Next
Worksheets("Macros Not Enabled").Visible = xlVeryHidden
End Sub
The .visible = xlVeryHidden makes the worksheet virtually impossible for a novice to get to since it doesn't even show up in the "Hidden list." The BeforeClose Event "very hides" all of the worksheets and displays the "Macros not enabled" worksheet. Next time a user opens it up with macros enabled, it hides "Macros not enabled" and displays all other worksheets. If a user doesn't enable macros, he opens up to the "Macros not enabled" worksheet and all others are still "very hidden." You can insert a note like "You must enable macros to use this workbook!" on the "Macros not enabled" worksheet.
Celia
