I'm trying to write a Template that has a splash screen as only
visible worksheet at the start and then unhides the other sheets based
on the user's access level

Admin unhides all sheets
R/W unhides all but the special sheets used for the macros
R/O unhides all but the special sheets used for the macros
None. nothing but splash screen

The code below works well for all access levels in Excel 2002.
In Excel 2000, it doesn't perform consistently. When I open the file
I get the MsgBox welcoming me, but nothing else happens. Next try, I
hit Ctrl-Break at the message box and entered debugger. The code
would run to the line tagged as having an error and stop (so we have
original configuration with only Splash screen visible). Here's the
weird part. If I were to run the Workbook_Open macro manually at this
point, the macro runs without a hitch.

The Active_Sheet range is located on a hidden sheet. My initial
thought was that Excel 2000 was having problem getting value fro
hidden sheet but that doesn't explain why the code runs without error
when run manually.

Any suggestions?

Terry Detrie


Private Sub Workbook_Open()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Initialize = True
Restrict = Range("Restrict").Value
Access = Range("Access").Value

If (Restrict Or Access = "R/O") And ActiveWorkbook.ReadOnly =
False Then
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
ElseIf Restrict = False And ActiveWorkbook.ReadOnly Then
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite
End If

If Access = "None" Then
MsgBox "You are not listed as a registered user."
Else
MsgBox "Welcome! Resetting pages now"

For Each Sh In ThisWorkbook.Worksheets
Range("Active_Sheet").Value = Sh.Name ' ERROR 1004
PassWd = Sheets("Sheets").Cells(2, 5).Value
If IsError(PassWd) Or Access = "Admin" Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetVeryHidden
If PassWd < "0" Then
Sh.Protect Password:=PassWd,
Userinterfaceonly:=True
End If
End If
Next Sh
End If

Initialize = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub