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 |
Happy VBA coding!