Setting The Proper Password Protection Using VBA Coding

Prevent Destruction

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.

Manual Examples

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.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

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

The Various Ways To Password Protect Excel Worksheets

Restricting Your User's Abilities Microsoft Excel's worksheet password protection options have been known to trip people up from time to...

Chris Newman

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.