How to Password Protect an Excel Worksheet Using a Macro

1. Open the Microsoft Excel spreadsheet you wish to protect.
2. Press 'ALT F11' to open Visual Basic.
3. Click on the 'Insert' menu and select 'UserForm.'
4. Click on the 'TextBox' tool and drag it onto the 'UserForm' work area. Adjust the size of the text box as desired.
5. Press 'F4' with the 'TextBox' still selected to enter the 'Properties' pane and scroll down to 'PasswordChar.' Insert an asterisk (*) in the value column.
6. Click on the 'UserForm' to bring the toolbox back and drag a 'CommandButton' onto the 'UserForm.' For aesthetic purposes, position the button to the right of the 'TextBox.'
7. Press 'F4' with the 'CommandButton' still selected and change the caption to 'OK.'
8. Click on the 'UserForm,' press 'F4' and change its caption to 'Protect/Unprotect All Sheets.'
9. Press 'F7' and paste the following code as it appears below:Private Sub CommandButton1_Click()Dim WSheet As WorksheetFor Each WSheet In WorksheetsIf WSheet.ProtectContents = True ThenWSheet.Unprotect Password:=TextBox1.TextElseWSheet.Protect Password:=TextBox1.TextEnd IfNext WSheetUnload meEnd Sub
10. Select 'Module' from the 'Insert' menu and paste the following:Sub ShowPass()UserForm1.ShowEnd Sub
11. Press 'ALT Q' to return to Excel.
12. Press 'ALT F8,' then press 'Options' after selecting 'ShowPass' from the list.
13. Assign a shortcut key to the macro and press 'OK.'
14. Press the shortcut key combination to launch the macro, enter a password, then press the 'OK' button to password-protect the sheets in the workbook.