VBA outline Row Levels

[Abvlecxe]

This will be easy peasy for people in here but i need a hand with my wee bit of code which groups or ungroups rows. Basically i want the VBA to establish if the sheet grouping is 1 then make it 2 but then stop. If the same button is pressed again the macro will see that grouping is 2 so then make it 1 and then stop. But when i press my button it's obviously running through all the code at the same time and therefore when it finishes in reality i'm back to how it started if that makes sense?

Sub group()
'
' group Macro
    
    Worksheets("P&L").Select
    
        If ActiveSheet.Outline.ShowLevels(RowLevels:=1) Then
        ActiveSheet.Outline.ShowLevels RowLevels:=2
         
       ElseIf ActiveSheet.Outline.ShowLevels(RowLevels:=2) Then
        ActiveSheet.Outline.ShowLevels RowLevels:=1
        
    End If
    
    End Sub

 

[nemmi69]

I don't think there is a worksheet property that reveals what level is displayed. I think this is because any row can manually hidden/unhidden as well as any group manually expanded/collapsed. The function below looks at the hidden property of rows and determine what groups are expanded or collapsed. Hope this helps.

Public Function LowestRowGroupLevelDisplayed( _
      Optional ByVal Worksheet As Worksheet _
   ) As Long
   
   Dim Row As Range
   Dim LowestLevel As Long
   
   If Worksheet Is Nothing Then Set Worksheet = ActiveSheet
   For Each Row In Worksheet.UsedRange.Rows.EntireRow
      If Not Row.Hidden Then
         If Row.OutlineLevel > LowestLevel Then LowestLevel = Row.OutlineLevel
      End If
   Next Row
   LowestRowGroupLevelDisplayed = LowestLevel
   
End Function


Sub testGRp()
    If (LowestRowGroupLevelDisplayed = 1) Then
        ActiveSheet.Outline.ShowLevels RowLevels:=2
    Else
        ActiveSheet.Outline.ShowLevels RowLevels:=1
    End If
End Sub

 

[Abvlecxe]

Perfect that works a treat, thanks very much!


QUOTE=nemmi69;5095753]I don't think there is a worksheet property that reveals what level is displayed. I think this is because any row can manually hidden/unhidden as well as any group manually expanded/collapsed. The function below looks at the hidden property of rows and determine what groups are expanded or collapsed. Hope this helps.

Public Function LowestRowGroupLevelDisplayed( _
      Optional ByVal Worksheet As Worksheet _
   ) As Long
   
   Dim Row As Range
   Dim LowestLevel As Long
   
   If Worksheet Is Nothing Then Set Worksheet = ActiveSheet
   For Each Row In Worksheet.UsedRange.Rows.EntireRow
      If Not Row.Hidden Then
         If Row.OutlineLevel > LowestLevel Then LowestLevel = Row.OutlineLevel
      End If
   Next Row
   LowestRowGroupLevelDisplayed = LowestLevel
   
End Function


Sub testGRp()
    If (LowestRowGroupLevelDisplayed = 1) Then
        ActiveSheet.Outline.ShowLevels RowLevels:=2
    Else
        ActiveSheet.Outline.ShowLevels RowLevels:=1
    End If
End Sub

[/QUOTE]

 

[Abvlecxe]

Hi nemmi69,

I've had to ditch the toggle button.

I now have a button which inserts rows into a spreadsheet and the user gets topick where, however the insert doesn't work properly when my rows are grouped,how to I amend the code so that when I click my insert rows button the codechecks the row levels and should the rows be grouped the code ungroups butshould they be un-grouped then that is fine and the code moves onto insertingmy rows?

This is mycurrent code:

Sub InsertRows()
Dim lastRow As Long
Dim Row1 As Long
Dim Row2 As Long
Dim myValue As Variant
Dim i As Long
Dim CancelTest As Variant
Dim Row As Range
Dim myPassword As String
myPassword ="Pass1"
Application.ScreenUpdating = False
lastRow = 0
myValue = InputBox("Enter Row Insert Line or Cancel toExit:")
If StrPtr(myValue) = 0 Then Exit Sub
With Sheet1
.Select
.UnprotectPassword:=myPassword
lastRow =Cells(Rows.Count, "C").End(xlUp).Row
Row1 = lastRow - 6
Row2 = lastRow
Rows(Row1 &":" & Row2).SelectSelection.Copy
End With
With Sheet1
.Select
Range("a" & myValue).Select
Selection.InsertShift:=xlDown
On Error GoTo 0
Application.CutCopyMode = False
lastRow = 0
.Range("c9").Select
.ProtectPassword:=myPassword, AllowFiltering:=True
End With
Application.ScreenUpdating = True
End Sub


Thanks v much

I don't think there is a worksheet property that reveals what level is displayed. I think this is because any row can manually hidden/unhidden as well as any group manually expanded/collapsed. The function below looks at the hidden property of rows and determine what groups are expanded or collapsed. Hope this helps.

Public Function LowestRowGroupLevelDisplayed( _
      Optional ByVal Worksheet As Worksheet _
   ) As Long
   
   Dim Row As Range
   Dim LowestLevel As Long
   
   If Worksheet Is Nothing Then Set Worksheet = ActiveSheet
   For Each Row In Worksheet.UsedRange.Rows.EntireRow
      If Not Row.Hidden Then
         If Row.OutlineLevel > LowestLevel Then LowestLevel = Row.OutlineLevel
      End If
   Next Row
   LowestRowGroupLevelDisplayed = LowestLevel
   
End Function


Sub testGRp()
    If (LowestRowGroupLevelDisplayed = 1) Then
        ActiveSheet.Outline.ShowLevels RowLevels:=2
    Else
        ActiveSheet.Outline.ShowLevels RowLevels:=1
    End If
End Sub

 

[nemmi69]

Sub Button()
' Ungroup
    If (LowestRowGroupLevelDisplayed <> 1) Then
        ActiveSheet.Outline.ShowLevels RowLevels:=1
    End If

' Do row insert stuff etc


'ReGroup
ActiveSheet.Outline.ShowLevels RowLevels:=2


End Sub

 

[Abvlecxe]

Perfect, thanks very much!

Sub Button()
' Ungroup
    If (LowestRowGroupLevelDisplayed <> 1) Then
        ActiveSheet.Outline.ShowLevels RowLevels:=1
    End If

' Do row insert stuff etc


'ReGroup
ActiveSheet.Outline.ShowLevels RowLevels:=2


End Sub