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. Are you an Excel user looking for a quick and easy way to unprotect multiple worksheets in your workbook at once? Having to manually unprotect one worksheet after another can be time consuming and tedious. With the help of this tutorial, you’ll learn how to create a macro that you can use with just two clicks of your mouse to instantly unlock all the sheets in any Excel file. Get ready to streamline your workflow and save precious time!
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.
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 to unprotect multiple Excel worksheets
Sub UnprotecyMySheets()
Dim password As Variant
Dim ws As Worksheet
On Error GoTo error
password = InputBox(“Password Please”)
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect password:=password
Next
Exit Sub
error: MsgBox “There is s problem with you Password – Please try again”
End Sub
Dont forget to SUBSCRIBE to the How To Excel At Excel YouTube Channel for more Excel solutions
