Setting The Proper Password Protection Using VBA Coding
Many times I use worksheet protection to prevent my users from accidentally breaking formulas or disrupting formatting. With VBA this can be even more important as oftentimes we may code our macros to perform on a static spreadsheet (nothing can be moved or it breaks). While if you talk to any programmer, they would say you need to make your code as dynamic as possible to account for any & every change a person might make; the reality is that we typically don’t have time to think up all the ways someone could break our code. We just want it to work and serve a value-added purpose.
TheSpreadsheetGuru VBA Protection Creator
While working on this article I somehow came up with this crazy idea to make writing this password protection stuff in VBA super easy. Below is what I came up with and I am letting you have it for free! Just click the download button at the end of this article. It’s completely unlocked, so you can take a peek at the VBA coding or make any modifications to its functionality.
Check out the following animated GIF to see it in action!
Protect Input Options (The Defaults)
Below are all the default inputs the Protect method has. The good thing is, if you don’t need to change one of the input values, you can remove it from your code. This will allow you to shorten (simplify) your code a little bit.
Sub Protect_Worksheet() 'PURPOSE: Show all the possible parameters for protecting a worksheet 'NOTE: The listed below boolean (T/F) values are the default values ActiveSheet.Protect Password:="ABC123", _ DrawingObjects:=True, _ Contents:=True, _ Scenarios:=True, _ UserInterfaceOnly:=False, _ AllowFormattingCells:=False, _ AllowFormattingColumns:=False, _ AllowFormattingRows:=False, _ AllowInsertingColumns:=False, _ AllowInsertingRows:=False, _ AllowInsertingHyperlinks:=False, _ AllowDeletingColumns:=False, _ AllowDeletingRows:=False, _ AllowSorting:=False, _ AllowFiltering:=False, _ AllowUsingPivotTables:=False End Sub
Unprotect A Worksheet
If you’re going to protect your worksheet with VBA, chances are you’re probably going to want to unprotect it at some point too. One of the biggest things that caused me trouble when first dealing with protection through VBA was not understanding that the protection pertained to macro execution as well.
What I mean by this is if I have specific cells currently protected on the spreadsheet my VBA macro wants to modify, the only way to make changes to those locked cells is to unprotect the spreadsheet. For those scenarios, the following piece of code comes in handy.
Make sure to lock the spreadsheet back up once your code has finished making its modifications!
Sub Modify_Protected_Worksheet() 'Unprotect Worksheet ActiveSheet.Unprotect "Password123" 'Make Your Modifications Range("A1").Value = 25 'Re-Protect Worksheet ActiveSheet.Protect "Password123" End Sub
Locked & Hide States For Various Excel Objects
By changing a Locked status you are either enabling/disabling that particular object or cell to be affected by the worksheet protection. Depending on what you are dealing with you can also choose to lock the text (for example in an inserted shape) or hide the contents that are stored (ie a cell formula). There are various ways you can do this manually with your mouse or automatically through VBA coding.
VBA Automation Examples
Sub Change_Protection_Options() 'Single Cell ActiveSheet.Range("B17").Locked = False ActiveSheet.Range("B17").FormulaHidden = False 'Range of Cells ActiveSheet.Range("A1:Z100").Locked = True ActiveSheet.Range("A1:Z100").FormulaHidden = True 'All Cells in Worksheet ActiveSheet.Cells.Locked = True 'Shape/Image ActiveSheet.Shapes("Shape 1").Locked = True 'Scenario ActiveSheet.Scenario("Scenario 1").Locked = True ActiveSheet.Scenario("Scenario 1").Hidden = True 'Chart ActiveSheet.ChartObject("Chart 1").Locked = True End Sub
There’s Much, Much More!
There are so many things you can do in terms of modifying and ensuring worksheet protection with VBA. Hopefully, through this article, I was able to give you a good start. Please leave a comment below if you have any questions or have situations that maybe weren’t addressed in the article.
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Understanding Excel’s Password Security Methodology
How Secure is Excel? There is not a great deal of documentation out there about how Excel handles password security,...
Protect Worksheet But Allow Formatting & Hiding Of Rows Or Columns
What This VBA Code Does This snippet of code will give you a couple of scenarios where you can protect...
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.