Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-560498%22%20slang%3D%22en-US%22%3EHow%20to%20hide%20and%20unhide%20selected%20columns%20and%20worksheets%20sametime%20using%20one%20password%20in%20VBA%20code%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%3EI%20have%204%20worksheets%20in%20a%20workbook.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EWorksheets%201%20%26amp%3B%202%20are%20not%20hidden%20and%20worksheets%203%20%26amp%3B%204%20are%20veryhidden.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIn%20worksheet%202%2C%20columns%20E%20to%20H%20are%20hidden.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EHow%20do%20I%20write%20a%20VBA%20code%20to%20unhide%20the%20selected%20columns%20(from%20E%20to%20H)%20from%20worksheet%202%20and%20veryhidden%20worksheets%203%20%26amp%3B%204%2C%20using%20a%20password%3F%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CFONT%3ENote%3A%20These%20columns%20and%20worksheets%20should%20not%20open%20by%20any%20other%20method.%3C%2FFONT%3E%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-560498%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561808%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20hide%20and%20unhide%20selected%20columns%20and%20worksheets%20sametime%20using%20one%20password%20in%20VBA%20code%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F334010%22%20target%3D%22_blank%22%3E%40subhashgc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20a%20cell%20and%20then%20use%20the%20worksheet%20change%20event%20to%20any%20change%20that%20the%20user%20may%20make%20to%20its%20contents.%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20rng%20As%20Range%3CBR%20%2F%3EDim%20status%20As%20String%3CBR%20%2F%3EDim%20character%20As%20String%3C%2FP%3E%3CP%3ESet%20rng%20%3D%20Range(%22A1%22)%3CBR%20%2F%3ESet%20Target%20%3D%20Target.Cells(1%2C%201)%3C%2FP%3E%3CP%3EIf%20Not%20Intersect(Target%2C%20rng)%20Is%20Nothing%20Then%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3C%2FP%3E%3CP%3Estatus%20%3D%20rng.Value%3CBR%20%2F%3Erng.Value%20%3D%20checkStatus(status)%3CBR%20%2F%3Erng.Select%3C%2FP%3E%3CP%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20takes%20me%20to%20a%20regular%20module%20where%20I%20test%20for%20a%20valid%20password%20(which%20may%20differ%20from%20the%20actual%20worksheet%20protection%20password).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20checkStatus(ByVal%20pwd%20As%20String)%20As%20String%3CBR%20%2F%3EDim%20wkb%20As%20Workbook%3C%2FP%3E%3CP%3EcheckStatus%20%3D%20pwd%3CBR%20%2F%3EIf%20pwd%20%3D%20%22userpassword%22%20Then%20bUnprotect%20%3D%20True%3CBR%20%2F%3Epwd%20%3D%20%22actualpassword%22%3C%2FP%3E%3CP%3ESet%20wkb%20%3D%20ThisWorkbook%3CBR%20%2F%3EIf%20Not%20bUnprotect%20Then%3CBR%20%2F%3ECall%20unProtectWorkbookAndSheets(pwd)%3CBR%20%2F%3ECall%20protectAllSheets(pwd)%3CBR%20%2F%3Ewkb.Protect%20Password%3A%3Dpwd%2C%20Structure%3A%3DTrue%2C%20Windows%3A%3DFalse%3CBR%20%2F%3E'Range(%22home.cell%22)%20%3D%20vbNullString%3CBR%20%2F%3EActiveWindow.DisplayWorkbookTabs%20%3D%20False%3CBR%20%2F%3EElse%3CBR%20%2F%3ECall%20unProtectWorkbookAndSheets(pwd)%3CBR%20%2F%3EcheckStatus%20%3D%20%22open%22%3CBR%20%2F%3EActiveWindow.DisplayWorkbookTabs%20%3D%20True%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20seem%20to%20remember%20the%20catch%20being%20that%20even%20to%20change%20the%20status%20of%20a%20protected%20sheet%2C%20I%20have%20to%20unprotect%20it%20first.%26nbsp%3B%20The%20names%20of%20the%20sheets%20requiring%20regular%20protection%20or%20'Very%20Hidden'%20status%20are%20held%20as%20a%20global%20constants%3C%2FP%3E%3CP%3EConst%20strProtectedSheets%20%3D%20%22Contents%2CSection1%22%20'add%20to%20this%20list%20to%20protect%20other%20tabs%3CBR%20%2F%3EConst%20strHiddenSheets%20%3D%20%22Labels%2CRefValues%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20protection%20involves%20statements%20such%20as%3C%2FP%3E%3CP%3EvProtected%20%3D%20Split(strHiddenSheets%2C%20%22%2C%22)%3CBR%20%2F%3EFor%20i%20%3D%20LBound(vProtected)%20To%20UBound(vProtected)%3CBR%20%2F%3EWith%20wkb.Worksheets(vProtected(i))%3CBR%20%2F%3E.Protect%20Password%3A%3Dpwd%2C%20DrawingObjects%3A%3DFalse%2C%20contents%3A%3DTrue%2C%20userInterfaceOnly%3A%3DTrue%3CBR%20%2F%3E.EnableSelection%20%3D%20xlUnlockedCells%20'xlNoRestrictions%3CBR%20%2F%3E.Visible%20%3D%20xlSheetVeryHidden%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ENext%20i%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnprotection%20is%20simpler%3C%2FP%3E%3CP%3ESet%20wkb%20%3D%20ThisWorkbook%3CBR%20%2F%3Ewkb.Unprotect%20Password%3A%3Dpwd%3CBR%20%2F%3EFor%20Each%20wks%20In%20wkb.Sheets%3CBR%20%2F%3Ewks.Unprotect%20Password%3A%3Dpwd%3CBR%20%2F%3Ewks.Visible%20%3D%20xlSheetVisible%3CBR%20%2F%3ENext%20wks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20code%20picked%20out%20of%20a%20very%20old%20workbook%20which%20may%20or%20may%20not%20be%20functional%20but%20some%20of%20the%20ideas%20should%20carry%20over.%3C%2FP%3E%3C%2FLINGO-BODY%3E
subhashgc
New Contributor

I have 4 worksheets in a workbook. 

Worksheets 1 & 2 are not hidden and worksheets 3 & 4 are veryhidden.

In worksheet 2, columns E to H are hidden.

 

How do I write a VBA code to unhide the selected columns (from E to H) from worksheet 2 and veryhidden worksheets 3 & 4, using a password?

 

Note: These columns and worksheets should not open by any other method.

1 Reply

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies