VBA Question: Getting values from hidden sheets

[Terry Detrie]

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

 

[Jim Cone]

Terry,

All of your code still doesn't make complete sense to me.
However, I have made some "improvements".
I have no way of testing it, but give it a try and see what happens...
(Note the instruction line **)
"----------------------------------------------
Option Explicit

Private Sub Workbook_Open()
Dim varRestrict As Variant
Dim varAccess As Variant
Dim blnInitialize As Boolean
Dim strPassWd As String
Dim Sh As Excel.Worksheet

Application.DisplayAlerts = False
Application.ScreenUpdating = False
blnInitialize = True
varRestrict = Range("varRestrict").Value
varAccess = Range("varAccess").Value

If (varRestrict = "R/O") Or (varAccess = "R/O") And _
ActiveWorkbook.ReadOnly = False Then

ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
ElseIf varRestrict = False And ActiveWorkbook.ReadOnly Then
ActiveWorkbook.ChangeFileAccess Mode:=xlReadWrite
End If

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

strPassWd = Sheets("Sheets").Cells(2, 5).Text
For Each Sh In ThisWorkbook.Worksheets
'** Insert actual name of the hidden sheet in next line... **
Sheets("hiddensheetname").Range("Active_Sheet").Value = Sh.Name
If IsError(strPassWd) Or (varAccess = "Admin") Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetVeryHidden
If strPassWd <> "0" Then
Sh.Protect Password:=strPassWd, Userinterfaceonly:=True
End If
End If
Next ' Sh
End If

blnInitialize = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'-------------------------------------
Regards,
Jim Cone
San Francisco, CA

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

-snip-