So I have a file with 19 sheets, 18 of those sheets are hidden with the 1 unhidden one being the Home tab. On the home tab there are cells that take you to the hidden sheets when they are double clicked. Once you get to the hidden sheet you are prompted for a password and that will either hide the sheet and send you back to the home page or it will give you access to the sheet. Before I password protected each sheet so it would just send you back I had locked all of the sheets and password protected them and every time you hit save it would collapse all of the groups, lock the page, and send you back to the hone sheet. Now that I've password protecting each sheet I don't need to lock the sheets but I still want them to collapse and send the user back to the home page when they hit save. Below is the code that I'm using to do this, but what I don't know is what do I need to get rid of to keep the functionality the same but remove locking the sheets?
For the home (Quick Look) sheet:
Private Sub Worksheet_Activate()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
If sh.Name <> ("Quick Look") Then
sh.Visible = xlSheetHidden
End If
Next sh
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Address
Case "$A$1"
Sheets("Axel").Visible = True
Sheets("Axel").Activate
Case "$A$11"
Sheets("Billy").Visible = True
Sheets("Billy").Activate
Case "$A$21"
Sheets("Charles").Visible = True
Sheets("Charles").Activate
End Select
End Sub
For each hidden sheet:
Private Sub Worksheet_Activate()
Const Password As String = "Axel"
If InputBox("Please input the password to view this sheet.") <> Password Then
Me.Visible = xlSheetHidden
Else
Me.Visible = xlSheetVisible
End If
End Sub
And finally for the workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sh As Object
For Each sh In ThisWorkbook.Sheets
If TypeName(sh) = "Worksheet" Then
On Error Resume Next
sh.Outline.ShowLevels RowLevels:=1
On Error GoTo 0
End If
Next sh
Set sh = Nothing
Sheets("Axel").Protect "Axel"
Sheets("Billy").Protect "Billy"
Sheets("Charles").Protect "Charles"
Sheets("Quick Look").Activate
End Sub
Bookmarks