Enable users to click"+" to group/ungroup in a protected sheet - VBA

[Jaspal]

Hello there,

I have two public macro's to protect and unprotect my sheet, which I use in other macros using "Call". I want users to be able to click "+" to group/ungroup columns and rows.
Public Sub UnprotectSheet(sWhichSheet)
' This MAcro unprotects the sheet
If Sheets(sWhichSheet).ProtectContents = True Then
Sheets(sWhichSheet).Unprotect Password:=sProtectionPassword
End If
End Sub

Public Sub ProtectSheet(sWhichSheet)
' This Macro protects the Sheet
If Sheets(sWhichSheet).ProtectContents = False Then
Sheets(sWhichSheet).Protect Password:=sProtectionPassword, DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True, AllowDeletingRows:= _
True
End If
End Sub



I tried using the below code in the Workbook_open, but it does not work. I get an error message that to group/ungroup i need to unprotect my sheet. I have also checked "Enable AutoFilter" in the protect worksheet option.

Private Sub Workbook_Open()
With Worksheets(Detailed)
.Protect Password:="protect", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

I also tried, but it does not work either
Private Sub Workbook_Open()
With Worksheets("Detailed")
Call UnprotectSheet(ActiveSheet.Name)
.Protect Password:="protect", UserInterfaceOnly:=True
.EnableOutlining = True
End With
Call ProtectSheet(ActiveSheet.Name)
End Sub



Thank you in advance for help.

[p45cal]

Could it be in one sub you have:
With Worksheets(Detailed)
and the other you have:
With Worksheets("Detailed")

..and is the Detailed sheet always going to be the active sheet on workbook opening?

[Jaspal]

Hello p45cal,

I tried both with quotes and unquote i.e. (Detailed) & "Detailed" in both the subs.

No Detailed sheet will not always be the active sheet, but I wanted to see it work fully in one sheet e.g. Detailed

[p45cal]

This worked for me:Private Sub Workbook_Open()
With Worksheets("Detailed")
.EnableOutlining = True
.Protect Password:="protect", UserInterfaceOnly:=True
End With
Call ProtectSheet("Detailed")
End Sub
The above code is in the ThisWorkbook code-module isn't it?
You can check that it runs on file opening by putting a Stop instruction in that macro (at the top) which should open the vbe with that line highlighted when you open the spreadsheet file.

[Jaspal]

Thank you p45cal, the Macro above worked.
Just one more question - how can I get this macro to work for all the sheets in the workbook? Also, if I wanted the macro to work for two sheets only "Detailed" & "Sheet2"?

I unsucessfully tried below for all the sheets in the workbook -

Private Sub Workbook_Open()
With Worksheets(ActiveSheet.Name)
.EnableOutlining = True
.Protect Password:="protect", UserInterfaceOnly:=True
End With
Call ProtectSheet(ActiveSheet.Name)
End Sub

[p45cal]

how can I get this macro to work for all the sheets in the workbook?
try:Private Sub Workbook_Open()
For Each sht In ThisWorkbook.Worksheets
sht.EnableOutlining = True
sht.Protect Password:="protect", UserInterfaceOnly:=True
'Call ProtectSheet(sht.name) 'I wouldn't bother with this line at all.
Next sht
End Sub


Also, if I wanted the macro to work for two sheets only "Detailed" & "Sheet2"?try:Private Sub Workbook_Open()
For Each sht In ThisWorkbook.Sheets(Array("Detailed", "Sheet2"))
sht.EnableOutlining = True
sht.Protect Password:="protect", UserInterfaceOnly:=True
'Call ProtectSheet(sht.name) 'I wouldn't bother with this line at all.
Next sht
End Sub

[Jaspal]

Thank you p45cal. It works brilliantly!