@subhashgc
I use a cell and then use the worksheet change event to any change that the user may make to its contents.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim status As String
Dim character As String
Set rng = Range("A1")
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
status = rng.Value
rng.Value = checkStatus(status)
rng.Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
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
End Function
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.
