How can I Collapse or Expand Outline in Protected Sheets

[Gos-C]

Hi,

I have outline on three protected worksheets in a workbook, and I want other users to be able to collapse and expand the outline while the worksheets are protected. I entered the following code (which works fine):

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

I then try the following (but it's not working):

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
With Worksheets("REPORT2")
With Worksheets("REPORT3")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Any help, please?

Thank you,
Gos-C

[Bryan Hessey]

Hi,

I have outline on three protected worksheets in a workbook, and I want other users to be able to collapse and expand the outline while the worksheets are protected. I entered the following code (which works fine):

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

I then try the following (but it's not working):

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
With Worksheets("REPORT2")
With Worksheets("REPORT3")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Any help, please?

Thank you,
Gos-C

Hi,

try a variation of

Please Login or Register  to view this content.

Let me know how you go
---

[Gos-C]

Hi Bryan,

It didn't work.

I continue to get the message:

You cannot use this command on a protected sheet. To unprotect the sheet, use the unprotect sheet command (Tools menue, Protection submenu). You may be prompted for a password.

Gos-C

[Bryan Hessey]

Hi Bryan,

It didn't work.

I continue to get the message:

You cannot use this command on a protected sheet. To unprotect the sheet, use the unprotect sheet command (Tools menue, Protection submenu). You may be prompted for a password.

Gos-C

Hi,

I can.

To test for yourself, start Macro recorder, then manually go Tools, Protection, Protect Sheet, and tick the third item (format cells).

Then rightmouse and format the cell.

Stop recording and compare this to your code.

Let me know how you go.
---

[Gos-C]

Hi Bryan,

I noticed you suggested code has "AllowFormattingCells." Would that allow the outlining level symbols to work when the sheet is protected?

The following works for one sheet:

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Can this be modified to work for three sheets in the same workbook?

Thanks for you help.

Gos-C

[Bryan Hessey]

Hi Bryan,

I noticed you suggested code has "AllowFormattingCells." Would that allow the outlining level symbols to work when the sheet is protected?

The following works for one sheet:

Option Explicit
Sub auto_open()
With Worksheets("REPORT1")
.Protect Password:="test", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

Can this be modified to work for three sheets in the same workbook?

Thanks for you help.

Gos-C

Hi,

yes, you need to amend

With Worksheets("REPORT1")

and set in a loop, thus

dim ws as worksheet
for each ws in worksheets
~~ code here
next ws

You may need to exclude some sheets, skip the code if .name = etc

Let me know how you go.

---

[Gos-C]

Thank you very much, Bryan.

I entered:

Option Explicit
Sub auto_open()
Dim ws As Worksheet
For Each ws In Worksheets
With ws 'Worksheets("REPORT1")
.Protect Password:="test", UserInterfaceOnly:=True
.EnableOutlining = True
End With
Next ws

End Sub

. . . and, it worked!

Gos-C

[Bryan Hessey]

Thank you very much, Bryan.

I entered:

Option Explicit
Sub auto_open()
Dim ws As Worksheet
For Each ws In Worksheets
With ws 'Worksheets("REPORT1")
.Protect Password:="test", UserInterfaceOnly:=True
.EnableOutlining = True
End With
Next ws

End Sub

. . . and, it worked!

Gos-C

Great to see a resolution, and thanks for the response.

---