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…

cloudy moodYes, 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

 

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.