Macros are an effective way to achieve certain tasks that are not possible using common Excel commands. One such task involves protecting and unprotecting multiple Excel sheets instantaneously. Just as Excel allows you to only protect one worksheet at a time, unprotecting multiple worksheets is also a manual process. However, you can make use of macros to automate the task and save yourself a lot of time. In this post, I’ll show you how to unprotect multiple worksheets using a simple excel macro.
- Tip: If you'd like to learn about how to create macros without writing them from scratch, check out our tutorial on Creating Macros Using the Macro Recorder.
How to Unprotect Multiple Worksheets
To unprotect multiple worksheets in one go using an Excel Macro:
Basic Macro Info | |
|---|---|
Level of Difficulty | Easy |
Works On | Excel 2007, 2010 |
Description | This macro will allow a user to simultaneously unprotect multiple worksheets in a workbook. |
Code:
Unprotect_selected_sheets()
Set wrksheet = ActiveWindow.SelectedSheets
For Each ws In wrksheet
ws.Select
ws.Unprotect Password:="password123"
Next ws
End Sub
Note: You need to use the same password you used to protect your worksheet.
How to Unprotect Multiple Worksheets
1. Copy the code.
2. Open the Excel worksheet that has protected worksheets and Ctrl+click the worksheets that you want to unprotect.
3. Press Alt + F11 to enter the Visual Basic Editor. If necessary, press Ctrl + R to show the Project Explorer.
4. Right-click This Workbook.
5. Choose Insert -> Module.
6. Paste code into the right pane.
7. Save the workbook in the XLSM format.
8. Press Alt + Q to close the VBE.
9. Select the Developer tab. (If the Developer tab is not displayed, please read our post How to Activate the Developer Tab in Excel 2010.
10. On the Developer tab, in the Code group, click Macros.
11. In the Macro dialog box, select the macro and click Run.
Make use of this simple macro to unprotect selected worksheets in one go.
Leave a Reply