MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please fix this easy code for me...


Posted by Bobi on January 29, 2001 5:30 AM

Could someone please fix this code for me..thanks..
When I run it...I get an error..

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 'this line turned to red
Next
End Sub

Private Sub Workbook_Open()
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = True 'this line turned to red
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 even insert a msg note like "You
must enable macros to use this workbook!!!!!" on the "Macros NOt Enabled" worksheet.


Posted by Celia on January 29, 2001 7:42 AM


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

Posted by Bobi on January 30, 2001 2:56 AM

Thanks Celia.nice jobs