How Do I Quickly Protect/Unprotect Worksheets in My Spreadsheet?

Analysis is finished! You’ve got all your pivot tables in place, now all you need to do is prep your spreadsheet for your audience  – i.e. people should not be able to view your calculation tabs and edit your analysis tabs 🙂

Easy, peasy! Just hide and protect!

Yet, if you have a couple of analysis tabs (trends, portfolio, per segment, MMixTrigols) and a couple of calculation tabs (Industry Dashboard, Firm R numbers, Products-TRIGOLS, Products-SQUAZOLS, charting), it could take a good 15 minutes doing the mind-numbing tasks of hiding sheets, pressing the “Protect Sheet” button and entering a password.

Further still, whenever you’d like to make a change, you’d have to go through the whole process again…

Yes, pretty annoying, right?

But no worries, if you use the short VBA snippet below, you’ll be done in no time 😉

 

	Sub KeepData()
	Dim Wks As Worksheet
	'~~~~~~~~~~~~~~~~~~~~~~
	'written by Angelina Teneva
	'toggling sheet and workbook protection on/off with a password
	If ActiveWorkbook.ProtectStructure = True Then
	ActiveWorkbook.Unprotect ("annie")
	For Each Wks In ActiveWorkbook.Worksheets
	If Wks.Visible = False Then Wks.Visible = True
	If Wks.Visible = xlSheetVeryHidden Then Wks.Visible = True 'unhides all very hidden sheets
	Wks.Activate
	If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect ("annie")
	Next Wks
	Else
	'--------hide confidential sheets (comment out if necessary)---------------------
	For Each Wks In ActiveWorkbook.Worksheets
	If Wks.name = "Firm R numbers" _
	Or Wks.name = "Industry Dashboard" _
	Or Wks.name = "charting" _
	Or Wks.name Like "Products*" _
	Or Wks.name Like "MResearch*" Then Wks.Visible = xlSheetVeryHidden
	Next Wks
	'--------protect wbk and visible sheets-------------------------------------------
	ActiveWorkbook.Protect ("annie"), Structure:=True
	For Each Wks In ActiveWorkbook.Worksheets
	If Wks.Visible = True Then Wks.Activate
	ActiveSheet.Protect ("annie"), DrawingObjects:=True, Contents:=True, _
	Scenarios:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
	Next Wks
	End If
	End Sub

view raw ForEachWks_ToggleWksProtection.bas hosted with ❤ by GitHub

 

Happy VBA coding!