Excel VBA: Allow users to click“+” to group/ungroup on multiple protected worksheets

1
votes

I found an Excel VBA solution to allow users to expand/collapse grouped rows and columns, while keeping the worksheet protected, here: http://www.mrexcel.com/forum/excel-questions/711327-allow-users-click-group-ungroup-protected-sheet-visual-basic-applications.html

For a single worksheet my code looks like this:

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

Note: I left the password for the worksheet empty (string: "")

However, I need to allow users to do the same thing on another worksheet called "CPM" as well. To acheive this I came up with the following code:

Private Sub Workbook_Open()
    Dim sheetsArray As Sheets
    Set sheetsArray = ActiveWorkbook.Sheets(Array("Master data", "CPM"))

    Dim sheetObject As Worksheet

    For Each sheetObject In sheetsArray
    With Worksheets(sheetObject)
        .Protect Password:="", UserInterfaceOnly:=True
        .EnableOutlining = True
    End With
    Next sheetObject
End Sub

The line

With Worksheets(sheetObject)

causes na error (Excel indicates the types don't match: I imagine Excel requires a string here).

Is there a quick fix to resolve this?

Kind regards,

Dennis

1
arraysvbaexcel
That works brilliant. Thanks! – Dennis

1 Answers

0
votes

Use

With sheetObject

instead of

With Worksheets(sheetObject)