Let me preface this article by saying that this will not help you recover lost data, or gain access to protected data that you otherwise wouldn’t have access to. What it will do is allow you to unlock a password protected worksheet in Excel, so that you can edit it as necessary. Simply put, if you don’t already have access to the worksheet, this macro will not help you.
Now that the disclaimer is out of the way, let’s figure out why people password protect worksheets to begin with. By protecting a worksheet and the contents of locked cells, users can keep themselves or other users from:
- accidentally (or intentionally) removing formulas, or other contents of locked cells
- adding or deleting rows and columns
- changing cell, column or row formats
- sorting data
- using AutoFilter or PivotTable reports
- editing objects or scenarios
Protecting a worksheet will not keep users from
- editing any unlocked cells in the worksheet
- viewing all data in the worksheet, regardless of if it is in a locked cell or not
Being able to unlock a password protected worksheet is useful, when
- you have forgotten the password on your own worksheet
- a co-worker, or other user has password protected a worksheet that you now need to edit, and they are not available to unlock the sheet for you
- you have a need to perform an analysis of the data in a password protected worksheet, but are unable to do so due to the locked cells
- you would like to sort/filter the data in a password protected worksheet, or create a PivotTable report from the data
Since Microsoft Excel is not a “secure” program, it is very easy to unlock the password of a password protected worksheet. This is because there are several different combinations of passwords that Excel will accept to unlock the worksheet. For example, a worksheet with the password “treehouse”, can also be unlocked with the password “AAAABAABBBB/”. The opposite is also true (i.e. protecting with the password “AAAABAABBBB/” can be unlocked with the password “treehouse”). Go ahead; try locking a worksheet with the password treehouse, and use AAAABAABBBB/ to unlock it.
The macro code below will “unlock” one worksheet at a time, using the method used above. It won’t provide you with the actual password someone typed in, but rather a random sequence of letters or symbols that will work to unlock the worksheet.