Excel VBA tips: How to Run Macros When a Workbook or Worksheet is Protected

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!

Submeg

IT