That takes me to a regular module where I test for a valid password (which may differ from the actual worksheet protection password).
Function checkStatus(ByVal pwd As String) As String Dim wkb As Workbook
checkStatus = pwd If pwd = "userpassword" Then bUnprotect = True pwd = "actualpassword"
Set wkb = ThisWorkbook If Not bUnprotect Then Call unProtectWorkbookAndSheets(pwd) Call protectAllSheets(pwd) wkb.Protect Password:=pwd, Structure:=True, Windows:=False 'Range("home.cell") = vbNullString ActiveWindow.DisplayWorkbookTabs = False Else Call unProtectWorkbookAndSheets(pwd) checkStatus = "open" ActiveWindow.DisplayWorkbookTabs = True End If
I seem to remember the catch being that even to change the status of a protected sheet, I have to unprotect it first. The names of the sheets requiring regular protection or 'Very Hidden' status are held as a global constants
Const strProtectedSheets = "Contents,Section1" 'add to this list to protect other tabs Const strHiddenSheets = "Labels,RefValues"
The protection involves statements such as
vProtected = Split(strHiddenSheets, ",") For i = LBound(vProtected) To UBound(vProtected) With wkb.Worksheets(vProtected(i)) .Protect Password:=pwd, DrawingObjects:=False, contents:=True, userInterfaceOnly:=True .EnableSelection = xlUnlockedCells 'xlNoRestrictions .Visible = xlSheetVeryHidden End With Next i
Unprotection is simpler
Set wkb = ThisWorkbook wkb.Unprotect Password:=pwd For Each wks In wkb.Sheets wks.Unprotect Password:=pwd wks.Visible = xlSheetVisible Next wks
This is code picked out of a very old workbook which may or may not be functional but some of the ideas should carry over.