Setting The Proper Password Protection Using VBA Coding

Excel Worksheet Password Protection Using VBA Macro Code

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 often times 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 cam 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 below and you can sign up for my free newsletter and have this spreadsheet emailed to your inbox immediately.  It's completely unlocked, so you can take a peek at the VBA coding or make any modifications to it's functionality.  Check out the following animated gif to see it in action!

Click to enlarge animated GIF

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, _

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 it's modifications!  

This run-time error pops up if you try to run a macro that makes a modification to something that is currently protected

This run-time error pops up if you try to run a macro that makes a modification to something that is currently protected

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

Right Click on cell > Format Cells > Protection

Data Tab > What If Analysis > Scenario Manager

Right Click on Shape > Size & Properties > Properties

Right Click on Shape > Size & Properties > Properties


Right Click on Chart > Format Chart Area > Properties

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
  ActiveSheet.Shapes("Shape 1").Locked = True

  ActiveSheet.Scenario("Scenario 1").Locked = True
  ActiveSheet.Scenario("Scenario 1").Hidden = True

  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. 

As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section


You May Also Be Interested In...

My Password Recovery add-in will allow you to recover any lost password that was protected within Excel 2010 and prior.  The add-in runs in Excel 2007 - 2013 and can even remove passwords from multiple worksheet tabs at the same time!  Proceeds from this add-in go towards paying the costs to run this website and keeping it AD-FREE!  So check out the promotional page by clicking the button below to see if this add-in can ease your pain of dealing with forgotten worksheet passwords.

Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on FacebookTwitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru website.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) or on the Floating Share Bar to the left and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!