You cannot use this command on a protected sheet.

Question

I have created a worksheet in which I've group certain rows. When I protect the sheet, the group function is no longer useable, I get the following error message: "You cannot use this command on a protected sheet. To use this command, you must first unprotect the worksheet (Review tab, changes groups, unprotect sheet button) You may be prompted for a password".  

Is there a way that I can lock/protect specific cells yet still use the group function?

Thank you,

Michele

Answer 1

If you already have the outline/subtotals/autofilter applied, you can protect

the worksheet in code (auto_open/workbook_open??).

Option Explicit

Sub auto_open()

    With Worksheets("sheet1")

        .Protect Password:="hi", userinterfaceonly:=True

        .EnableOutlining = True

        '.EnableAutoFilter = True 

        'If .FilterMode Then

        '   .ShowAllData

        'End If

    End With

End Sub

It needs to be reset each time you open the workbook.  (Earlier versions of

excel don't remember it after closing the workbook.  IIRC, xl2002+ will remember

the allow autofilter setting under tools|Protection|protect sheet, but that

won't help when you're filtering via code.)

Maid4boys wrote:

I have created a worksheet in which I've group certain rows. When I protect the sheet, the group function is no longer useable, I get the following error message: "You cannot use this command on a protected sheet. To use this command, you must first unprotect the worksheet (Review tab, changes groups, unprotect sheet button) You may be prompted for a password".

Is there a way that I can lock/protect specific cells yet still use the group function?

Thank you,

Michele

--

Dave Peterson

Answer 2

If you want to apply the code to all worksheets:

Sub Auto_Open()

    Dim wsh As Worksheet

    For Each wsh In Worksheets

        With wsh

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

            .EnableOutlining = True

            '.EnableAutoFilter = True

            'If .FilterMode Then

            '   .ShowAllData

            'End If

        End With

    Next wsh

End Sub

and if you want to apply it to specific sheets only:

Sub Auto_Open()

    Dim wsh As Worksheet

    ' Specify the names of the sheets in the array

    For Each wsh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))

        With wsh

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

            .EnableOutlining = True

            '.EnableAutoFilter = True

            'If .FilterMode Then

            '   .ShowAllData

            'End If

        End With

    Next wsh

End Sub

Answer 3

What about for multiple worksheets? I tried adding another module but excel won'y allow 2 Auto_Open modules?.  I tried added 2 work sheet name ("Tab1", "Tab2) but got a Compile Error saying "Wrong number of arguments or invalid property assignment".

Ignore last post I sent, I resolved by doing the following ...

Sub auto_open()

With Worksheets("Tab1")

.Protect Password:="x", UserInterfaceOnly:=True, AllowDeletingRows:=False, DrawingObjects:=True, _

    Contents:=True, Scenarios:=True, AllowInsertingRows:=False, AllowFormattingCells:=True, _

    AllowFormattingRows:=True, AllowInsertingHyperlinks:=True

.EnableOutlining = True

End With

With Worksheets("Tab2")

.Protect Password:="x", UserInterfaceOnly:=True, AllowDeletingRows:=False, DrawingObjects:=True, _

    Contents:=True, Scenarios:=True, AllowInsertingRows:=False, AllowFormattingCells:=True, _

    AllowFormattingRows:=True, AllowInsertingHyperlinks:=True

.EnableOutlining = True

End With

End Sub

Answer 4

What about for multiple worksheets? I tried adding another module but excel won'y allow 2 Auto_Open modules?.  I tried added 2 work sheet name ("Tab1", "Tab2) but got a Compile Error saying "Wrong number of arguments or invalid property assignment".

Answer 5

You can use code to set the worksheet's EnableOutlining property to True. Seehttp://msdn.microsoft.com/en-us/library/bb221161(office.12).aspx