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 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 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!
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.
'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", _
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!
'Make Your Modifications
Range("A1").Value = 25
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
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
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.
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 Facebook, Twitter, 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!