How do you run macros upon opening Excel workbook, then re-protect after finished running?

Question

Is there a way to first, unprotect a workbook at the beginning of opening, run all the workbook_open() macros and then re-protect the workbook so that a common user can not change anything on any worksheets? I'd like to prevent the common user from changing anything but allow the macros to do its auto-update and computations.

Any suggestions? Is this even feasible?

See if this helps: http://stackoverflow.com/questions/16174469/unprotect-vbproject-from-vb-code — Raptor, Nov 04 '13 at 02:42
`Workbook_Open` events are tricky to deal with, especially with multiple instances. I have seen one or two times where they screwed up my work as well. Instead of lining up `Open` events, what I did was use a first `Open` event and just placed everything else into a `Worksheet_Activate` event. Can this work for you? — WGS, Nov 04 '13 at 03:02
Ensure your `Private Sub Workbook_Open()` is in `ThisWorkBook` Object. All the procedures can be stored in a regular Module (as long as not `Private Sub`). Also use `ThisWorkbook` instead of `ActiveWorkbook`, avoid anything with `Active...`/`.Activate`/`.Select` in your codes. I would create `Unprotect`, `ReProtect` subs too. — PatricK, Nov 04 '13 at 05:12
@ShivanRaptor I think the OP is referring to unprotecting a sheet/sheets, not unprotecting a VBA project (although that link is a great resource in the event the OP is referring to that). :) — ARich, Nov 04 '13 at 05:22

ARich · Accepted Answer · 2013-11-04T20:01:19.923

What I think you're looking for is the Worksheet.Protect method.

At the beginning of your Workbook_Open event, place this code:

ThisWorkbook.Sheets("SheetYouWantToUnprotect").Unprotect _   Password:="TheSheetPassword"

Then at the end of your Workbook_Open event code, you want to protect the sheet again, so place this code:

ThisWorkbook.Sheets("SheetYouWantToProtect").Protect _  
Password:="SheetPassword"

Of course, you can do this with more than one sheet if multiple sheets need to be unprotected. If each sheet has the same password, place a simple loop at the beginning of your Open event:

Dim sht as Worksheet  

For Each sht in Thisworkbook.Sheets  
    sht.Unprotect Password:="YourPassword"  
Next sht

At the end of your code you can use the same loop to protect the sheets, just change .Unprotect to .Protect.

Or, if there are many different passwords (aside from recommending you change them all to one password), you can place as many instances of the first line of code I mentioned as it takes to unprotect the necessary sheets.

Note: There are many more optional arguments to the .Protect method which I did not cover, but you can find an exhaustive list here.

EDIT: Here's more info on the Unprotect method.

How come when I stick in the ThisWorkbook.RefreshAll in between the unprotected loop and the "protect" loop I get the "cannot edit pivot table in protected sheet? — crazian, Nov 10 '13 at 03:10

score 1 · Answer 2 · answered Nov 04 '13 at 11:53

Yes, it can be done

Private Sub Workbook_Open()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Sheets
    WS.Unprotect Password:="YourPassword"
    WS.Protect Password:="YourPassword", UserInterfaceOnly:=True
Next WS
End Sub

The UserInterfaceOnly:=True allows VBA to act on any part of your worksheets while the common user can only interact with "unlocked" cells.

How do you run macros upon opening Excel workbook, then re-protect after finished running?

2 Answers2