Happy Monday!!. Time for some more Monday Macro fun. I wanted to share this little but powerful Macro with you today, I used this myself just last week so thought you would like to have this in your Excel Macro toolkit.
I had a workbook with 14 worksheets all protected, as they all contained a lot of formulas which I did not want any users of the workbook to be able to edit in any way. A small piece of code unlocked them all at the same time.
Download Macro Mondays Example Files.
What Does The Macro Do?
The Macro will use a message box to prompt the user to enter the password to unlock all of the worksheets protected in the Excel workbook. Excel will loop through all of the worksheets and unlock them if the password is correct. If the password is incorrect then a message box will appear to user informing them, with a message we have created.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Module
Step 1. We need to declare some variables. We need to declare the password as variant as it could contain any type of, or a mix of characters and worksheet is declared also as ws. This will create a memory container in Excel for each worksheet the macro loops through.
Step 2. Next, if the user enters an incorrect password, an error message box is prompted to inform the user of the message we choose in Step 5.
Step 3. The macro begins with a input box prompting the user for their password and Excel begins at the first work sheet in the workbook, using the password supplied by the user in the input box and un-protects the worksheet.
Step 4. Excel then loops through the next worksheet and so on until all worksheets have been completed and the macro ends.
Step5. We can choose what ever we want to the error to say, if the error routine is prompted by the user by entering an incorrect password.
Step 6. Test Your Macro!
Want The Code? You can copy it right here
Dim password As Variant
Dim ws As Worksheet
On Error GoTo error
password = InputBox(“Password Please”)
For Each ws In ActiveWorkbook.Worksheets
error: MsgBox “There is s problem with you Password – Please try again”
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.