Collapse/Expand groupings in protected worksheet

[Linki]

Hi all

Yesterday I had some help trying to make it so that people could expand and collapse existing groups within a protected worksheet.
Adding this code to workbook:

Private Sub workbook_open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Protect Password:="Test", userinterfaceonly:=True
ws.EnableOutlining = True
Next
End Sub

It worked well for my helper, but it did not work for me.
I'm using an O365 business license and excel is updated to the latest version.
We were using the same file for the test, and I made attempts on different computers - however, same version of excel.
I'm based in Europe, and the person assisting me is based in North America - could there be something regional?

When I try to expand or collapse the groups while the sheet is protected I keep getting faced with this error:

Would anyone have ideas as to what could be the issue?

Thank you!

 

[Candyman8019]

I tried this on two different computers. One domain joined with a bunch of security and one personal…in both cases I was able to expand and collapse groups on the protected sheet.

 

[Linki]

I dont know if it could be related to my settings? These are my macro settings I've never modified these:

 

[Candyman8019]

Mine are the same except I don't have the 4.0 macros enabled...

 

[Candyman8019]

After more testing...it turns out that Excel doesn't like having an ! at the start of a password. I haven't tried with other special characters, but I assume it would be true for those as well.

 

[RoryA]

Also, you should really be using:

For Each ws In ThisWorkbook.Sheets

not:

For Each ws In ActiveWorkbook.Sheets

 

[Linki]

After more testing...it turns out that Excel doesn't like having an ! at the start of a password. I haven't tried with other special characters, but I assume it would be true for those as well.

This worked! Thank you! (at least after I found out the IT team had a setting in place that per default locked xlsm files downloaded).

 

[Linki]

Also, you should really be using:

For Each ws In ThisWorkbook.Sheets

not:

For Each ws In ActiveWorkbook.Sheets

Thanks Rory!
Out of curiosity, using one or the other, what is the advantage / disadvantage? Very new to this.

 

[RoryA]

ThisWorkbook always refers to the workbook with the code in it, whereas Activeworkbook refers to whichever workbook is active at the time.

 

[jodre]

I've found a solution without using macros. When you click "Protect Sheet" you are prompted to set a password and check a number of settings ("Allow all users of this worksheet to:").

Check the boxes: "Format columns" and "Format rows". This will allow you to collapse and expand groupings while your worksheet is protected.