VBA to test Outline levels

[AllisterB]

I am wanting to give the user the choice to expand the Outline Levels or not. I first want to check that the levels are not fully expanded already. However my code below errors on the first IF. Can someone help me with this?

Thank You

Private Sub Worksheet_Activate()

'Anchors Freeze Frame
    ActiveWindow.FreezePanes = False

    Range("r_Anchor").Select
    
    ActiveWindow.FreezePanes = True
    'Protection to allow other macros to run on the sheet and to allow Filter and Grouping
 
    Me.Protect DrawingObjects:=True, Contents:=True, UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingRows:=True, _
    AllowInsertingRows:=True, AllowFormattingCells:=True
Me.EnableOutlining = True

If Me.Outline.ShowLevels RowLevels:=4 and Me.Outline.ShowLevels ColumnLevels:=4 Then Exit Sub
If MsgBox("Do you want to Expand the  Rows and Columns", vbYesNo) = vbNo Then Exit Sub

'Expand Data Group Rows
  Me.Outline.ShowLevels RowLevels:=4, ColumnLevels:=2


End Sub

 

[onlyadrafter]

Hello,

think i may have found a workround, change the IF code line to

If Columns("A:A").SpecialCells(xlCellTypeVisible).Count = Columns("A:A").Cells.Count Then Exit Sub

any use?

 

[AllisterB]

Thanks for this

I tried but got a 1004 error.

Private Sub Worksheet_Activate()

'Anchors Freeze Frame
    ActiveWindow.FreezePanes = False

    Range("r_Anchor").Select
    
    ActiveWindow.FreezePanes = True
    'Protection to allow other macros to run on the sheet and to allow Filter and Grouping
 
    Me.Protect DrawingObjects:=True, Contents:=True, UserInterfaceOnly:=True, AllowFiltering:=True, AllowFormattingRows:=True, _
    AllowInsertingRows:=True, AllowFormattingCells:=True
Me.EnableOutlining = True



If Columns("A:A").SpecialCells(xlCellTypeVisible).Count = Columns("A:A").Cells.Count Then Exit Sub
If MsgBox("Do you want to Expand the  Rows and Columns", vbYesNo) = vbNo Then Exit Sub

'Expand Data Group Rows
  Me.Outline.ShowLevels RowLevels:=4, ColumnLevels:=2


End Sub

 

[onlyadrafter]

Hello,

at what line are you getting the error?

The code works for em, with two group of rows and one group of columns

 

[AllisterB]

All goodnow - thanks