How to hide and unhide selected columns and worksheets sametime using one password in VBA code?

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.