Excel VBA tips: How to Run Macros When a Workbook or Worksheet is Protected
Have you created a workbook which contains macros, but you wish to protect the sheet? When you protect the sheet, it stops the macros from running, which is really annoying. However, there is a way around this.
1. Open up VBA Editor
2. In the project pane on the left hand side of the screen, select the sheet “ThisWorkbook”
3. Enter the following code:
Private Sub Workbook_Open() 'If you have different passwords for each Worksheet. Sheets(1).Protect Password:="Secret", _ UserInterFaceOnly:=True Sheets(2).Protect Password:="Carrot", _ UserInterFaceOnly:=True 'Repeat as needed. End Sub
for sheets that have the same password, you can use the following code:
Private Sub Workbook_Open() Dim wSheet As Worksheet For Each wSheet In Worksheets wSheet.Protect Password:="Secret", _ UserInterFaceOnly:=True Next wSheet End Sub
Now you can protect your worksheets from people modifying them, but still allow authorised macros to be run.
Come check out the directory for the rest of my excel tips!