How to Protect & Unprotect in Excel VBA
- 1). Open the Excel workbook that you want to work with. Hold "Alt" and press "F11" to launch the VBA console.
- 2). Select the module where you want to add your code from the list on the left side of the VBA console. Double-click the module to bring up your code on the right side of the screen. If no module is present, right-click on any one of the worksheets listed on the left side of the screen, move your mouse over "Insert" and choose "Module." Double-click the module that appears to bring up a blank sheet on the right side of the VBA console.
- 3). Click the right side of the screen to place your cursor into the white box. If the box is blank, type "Sub name()," where "name" is whatever you want to name your macro. Press enter to continue, and Excel VBA will automatically enter the "End Sub" line.
- 4). Type "Dim ws As Worksheet" directly underneath your "Sub" line. If you are working with existing code, enter this line wherever your other variables are declared and ensure that there isn't already another variable named "ws." If there is, change all instances of "ws" in this code to any available combination of two letters.
- 5). Place the cursor on the next line, or if you are working with existing code, place it wherever you want to unprotect the worksheet. Enter in the following code:
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="zzz"
Next ws
Change "zzz" in the code to whatever your worksheet password is. When you want to protect the worksheet again, just enter this same three lines of code, except change "unprotect" to "protect."
Source...