Hiden sheet code - need access

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

Hi All,

I love this code below, but I dont know how to access the sheet once
it is hidden.

1st Question - How do I access it using the password?

2nd Question - Is there anyway that I can set this workbook to reveal
the hidden sheet when I use the password to open (not as read only) or
is it macro based only?



Sub HideModeratelyWell()
With Sheets("YourSheetName")
.Protect Password:="ThisIsBreakable"
.Visible = xlVeryHidden
End With
End Sub
 
Sub HideModeratelyWell2()
With Sheets("YourSheetName")
.Unprotect Password:="ThisIsBreakable"
.Visible = xlSheetVisible
End With
End Sub
 
Thanx...

one more trick.

I am working with multiple hidden sheets.
Not sure how to change the code to fit multiple sheets.

With Sheets("INFO SHEET,ROSTER,TIMESHEET,PRODUCTION")
 
would help if we knew these were the only sheets in the workbook, but:

Sub unHideModeratelyWell2()
Dim shArray As Variant
Dim i As Long
shArray = Array("INFO SHEET", "ROSTER", "TIMESHEET", "PRODUCTION")
For i = LBound(shArray) To UBound(shArray)
With Sheets(shArray(i))
.Unprotect Password:="ThisIsBreakable"
.Visible = xlSheetVisible
End With
Next
End Sub

Sub HideModeratelyWell2()
Dim shArray As Variant
Dim i As Long
shArray = Array("INFO SHEET", "ROSTER", "TIMESHEET", "PRODUCTION")
For i = LBound(shArray) To UBound(shArray)
With Sheets(shArray(i))
.Protect Password:="ThisIsBreakable"
.Visible = xlVeryHidden
End With
Next
End Sub
 
»Gary Keramidas« said:
would help if we knew these were the only sheets in the workbook, but:

Sub unHideModeratelyWell2()
Dim shArray As Variant
Dim i As Long
shArray = Array("INFO SHEET", "ROSTER", "TIMESHEET", "PRODUCTION")
Click to expand...

Thanks, you just gave me the answer to my thread "VBA - array or
collection literals?"... so the MakeArray function isn't even needed.
However, I'll now stick to using the ParamArray anyway.
For i = LBound(shArray) To UBound(shArray)
With Sheets(shArray(i))
.Unprotect Password:="ThisIsBreakable"
.Visible = xlSheetVisible
End With
Next
End Sub
Click to expand...

Simplifying it a bit:

Sub unHideModeratelyWell2()
Dim s As String
For Each s In Array("INFO SHEET", "ROSTER", "TIMESHEET", "PRODUCTION")
With Sheets(s)
.Unprotect Password:="ThisIsBreakable"
.Visible = xlSheetVisible
End With
Next
End Sub

should do it.
 
Back
Top