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", _


	Sheets(2).Protect Password:="Carrot", _


'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", _


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!



Posted by:submeg

Musician, Writer and Inspirer. I discuss the projects I am working on, the services I provide and my general thoughts.

Tell me what you think!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s