In Excel 2010: I have a protected sheet and need to group/ungroup rows

The + or - show, but do not work when worksheet is protected

You could do the following:

• Press Alt+F11 to activate the Visual Basic Editor.
• Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer on the left hand side.
• Copy the following code into the workbook module:

Private Sub Workbook_Open()
    With Me.Worksheets("Sheet1")
        .EnableOutlining = True
        .Protect UserInterfaceOnly:=True, Password:="secret"
    End With
End Sub

• Replace Sheet1 with the name of your worksheet. If you don't want a password, omit the part
  , Password:="secret"
• Switch back to Excel.
• Save the workbook in a macro-enabled format (.xlsm, .xlsb or .xls)
• Close and reopen the workbook.
• You should now be able to use the outline buttons.

I get a error

Cannot do this command on a protected sheet

Is there a button command that allows ungroup and re-protect sheet

I am open to ideas, but I need to protect sheet to stop areas from being edit

Cannot do this command on a protected sheet

I am open to ideas, but I need to protect sheet to stop areas from being edit

I get the same error message. Furthermore the EnableOutlining property is (re-)set to False when I reopen the workbook.

The only way I know is to un-/hide rows, resp. columns with VBA. But that works only when you allow to format rows, resp. columns. See code below.

Otherwise you have to remove the protection temporary and use the ActiveSheet.Outline.ShowLevels method, as shown in the code below.

Andreas.

Sub Example_ShowLevels()
  'Show all rows
  ShowLevels ActiveSheet.UsedRange, 1, , False
  'Hides all rows from level 2 (same as if you click on the "2" manually)
  ShowLevels ActiveSheet.UsedRange, 2, , True
End Sub

Sub ShowLevels( _
    Optional Where As Range = Nothing, _
    Optional ByVal RowLevels As Integer = 0, _
    Optional ByVal ColumnLevels As Integer = 0, _
    Optional ByVal HideLevels As Boolean = False)
  'Toggles individual outline levels
  'Note:
  '  On protected sheets you must allow format rows/columns!
  '  ActiveSheet.Protect AllowFormattingColumns:=True, AllowFormattingRows:=True

  Const MaxLevels = 8
  Dim Used As Range, Area As Range, R As Range
  Dim FromR As Range, ToR As Range
  Dim SaveScreenUpdating As Boolean

  SaveScreenUpdating = Application.ScreenUpdating
  Application.ScreenUpdating = False

  If Where Is Nothing Then
    On Error Resume Next
    With ActiveSheet.Outline
      If RowLevels > 0 Then .ShowLevels RowLevels:=IIf(HideLevels, RowLevels, MaxLevels)
      If ColumnLevels > 0 Then .ShowLevels ColumnLevels:=IIf(HideLevels, ColumnLevels, MaxLevels)
      GoTo ExitPoint
    End With
  End If

  On Error GoTo ExitPoint
  Set Used = Intersect(Where, ActiveSheet.UsedRange)
  For Each Area In Used.Areas
    If ColumnLevels > 0 Then
      For Each R In Area.Columns
        If R.EntireColumn.OutlineLevel >= ColumnLevels Then
          If FromR Is Nothing Then
            Set FromR = R.EntireColumn
          Else
            Set ToR = R.EntireColumn
          End If
        Else
          If Not ToR Is Nothing Then
            Application.Range(FromR, ToR).Hidden = HideLevels
            Set FromR = Nothing
            Set ToR = Nothing
          End If
        End If
      Next
    End If

    If RowLevels > 0 Then
      For Each R In Area.Rows
        If R.EntireRow.OutlineLevel > RowLevels Then
          If FromR Is Nothing Then
            Set FromR = R.EntireRow
          Else
            Set ToR = R.EntireRow
          End If
        Else
          If Not ToR Is Nothing Then
            Application.Range(FromR, ToR).Hidden = HideLevels
            Set FromR = Nothing
            Set ToR = Nothing
          End If
        End If
      Next
    End If
  Next
ExitPoint:
  Application.ScreenUpdating = SaveScreenUpdating
End Sub