CODEWITHSUNDEEP
vbaexcel
I Hamilton
I Hamilton

Reputation: 13

VBA Expand/Collapse rows

I have a report in which I am asking the users to click buttons to reveal where they need to add their commentary. I have it working but wanted to put in an If statement in case they have already expanded the row.

I have two macros, the first relates to the button they push and sends to the main macro the name of the button and a row number which is part of the section that is either expanded or collapsed 

Sub ROccupancy()
'
Dim RecName As String
RecName = "ROccupancy"
Dim RowNum As Integer
RowNum = 27

Call ToogleRec(RecName, RowNum)

End Sub

The next macro is where I am having the trouble 

Sub ToogleRec(RecName, RowNum)
'
Dim Toogle As String
Dim MyObj As Object

Set MyObj = ActiveSheet.Shapes.Range(Array(RecName))

Toogle = Left(MyObj.TextFrame2.TextRange.Characters.Text, 4)
TextName = Mid(MyObj.TextFrame2.TextRange.Characters.Text, 5, 100)
If Toogle = "Show" Then
    MyObj.ShapeStyle = msoShapeStylePreset9
    MyObj.TextFrame2.TextRange.Characters.Text = _
        "Hide" & TextName
    MsgBox Rows(RowNum).ShowDetail
        If Rows(RowNum).ShowDetail = False Then
            Rows(RowNum).ShowDetail = True
        End If


Else
    MyObj.ShapeStyle = msoShapeStylePreset11
    MyObj.TextFrame2.TextRange.Characters.Text = _
        "Show" & TextName
    MsgBox Rows(RowNum).ShowDetail
        If Rows(RowNum).ShowDetail = True Then
            Rows(RowNum).ShowDetail = False
        End If
End If
Range("C" & RowNum).Select
End Sub

The issue is the Rows(RowNum).ShowDetail is always TRUE, no matter if it's expanded or collapsed. I can remove the If section and set it to TRUE or FALSE using "Rows(RowNum).ShowDetail = False" or "Rows(RowNum).ShowDetail = TRUE". However, if the user has manually expanded or collapsed the row it causes an error (which freaks them out)

This question and answer seemed promising but Rows(RowNum).ShowDetail always seems to be TRUE

I put the MsgBox in there for error checking. I'll remove it in the final version.

Upvotes: 1

Views: 3517

Answers (1)

L42
L42

Reputation: 19737

Have you tried using Hidden property? Something like:

With Sheet1.Rows(5)
    .ShowDetail = .Hidden
End With

Take note though that for you to use .ShowDetail method,
you'll need to Group the rows first (needs to be in outline form).

True if the outline is expanded for the specified range (so that the detail of the column or row is visible). The specified range must be a single summary column or row in an outline.

Above code toggles hiding/unhiding a grouped row 5.
You don't even need an If statement for the toggling. HTH.

Upvotes: 1

Related Questions