Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question: Getting values from hidden sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question: Getting values from hidden sheets
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").Va lue = 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 "Terry Detrie" wrote in message ... 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- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hidden sheets | Excel Discussion (Misc queries) | |||
Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
Hidden Sheets | Excel Worksheet Functions | |||
Help! Calling hidden sheets? | Excel Programming | |||
Help! Calling hidden sheets? | Excel Programming |