Refreshing a Macro on Protected Sheet when workbook reopened

Question

Hi

I am using the below code to allow grouping/ungrouping on each sheet in my workbook. I have added at sheet level rather than Workbook as sheets may be copied and I would like the macro to go with each copied sheet. Not all sheets are protected.

When a workbook is reopened, excel opens at the last sheet accessed, which in this case, may have the below code. The trouble is that the code only works when navigating to each sheet from another sheet so on the opened sheet it gives an error.. Is there some code that can be added that when the file is opened it runs the code on the opening sheet.

I could add some code to activate a particular sheet each time but was just wondering if this could be avoided?

Cheers

Jan

Option Explicit
 
Private Sub Worksheet_Activate() '......This macro allows Grouping/ungrouping on a Protected Sheet
 
ActiveSheet.EnableOutlining = True
ActiveSheet.Protect Password:="xyz", UserInterfaceOnly:=True

End Sub

Answer 1

I understand that you want to change the behaviour of Excel using a VBA code... for whatever reason.

Your question (how to run the code when the file is opened) was really simple and my answer is still pretty basic: Run the same code in Workbook_Open.

This can cause of course issues ... maybe the sheet is already protected... maybe it's not a worksheet... un-/protect sheets automatically using code is from my point of view not a good idea, but it's your file. 😉

Andreas.

Private Sub Workbook_Open()  

    With ActiveSheet

     .EnableOutlining = True      

       .Protect Password:="Demo", UserInterfaceOnly:=True     

 End With  

End Sub

Answer 2

Hi Andreas,

ahhhh.....I understand what you are saying now... from the first response, I didn't quite get that you meant to run the same code in "This Workbook". That makes sense as it activates the sheet that is first opened by Excel which is where the issue was.

So thanks for taking the time to explain further.

Cheers

Jan

Answer 3

Andreas

I referred to this article when setting up the code. https://www.spreadsheet1.com/outlining.html

It suggested using the Workbook Open Event or Worksheet Activate Event.

The current workbook has 25 protected sheets, but will be slimmed down and used as a master for future projects. The user will duplicate tabs according to the job specification. Thus I thought it better to use the Worksheet Activate event so the user does not have to keep reopening the workbook to activate the code on new duplicated sheets.

From the code below, I assume all sheets would need to referenced when opening the workbook which will mean updating the code, so how am I supposed to use your suggestion?

Jan

Option Explicit
 
' The following code goes in the ThisWorkbook module
 
Private Sub Workbook_Open()
 
    With ActiveWorkbook.Sheets("Demo")
 
       .Protect Password:="Demo", UserInterfaceOnly:=True
 
       .EnableOutlining = True
 
    End With
 
End Sub

Answer 4

Use the Workbook_Open event routine in the code module ThisWorkbook.

Andreas.