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
Highlighted
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
Highlighted

@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
Automatic login lost.
Jacques Van de Meerssche in Discussions on
0 Replies
Show users his posts only in a channel ?
Xemega in Microsoft Teams on
0 Replies
Making shifts not visible to all team members
rfloo in Microsoft Teams on
3 Replies
Password-protecting folder in Teams
DrewB37 in Microsoft Teams on
7 Replies