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

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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s