CODEWITHSUNDEEP
excelvba
user1717622
user1717622

Reputation: 323

VBA expand/collapse rows with same macro button

We have a trivial problem regarding how to run a simple macro button. The purpose of this button is two-fold: expanding a row and collapsing a row.

1 on pressing the button this VBA command is initiated:

Sub Macro7()

Rows(7).ShowDetail = True

End Sub

This command expands row 7.

2 on pressing the button again (whilst the row is expanded), this VBA is initiated:

Sub Macro7()

Rows(7).ShowDetail = False

End Sub

This collapses the row.

Is there a way to link a button to two macros?

Thanks in advance!!!

M

Upvotes: 6

Views: 30711

Answers (5)

Cuinn Herrick
Cuinn Herrick

Reputation: 317

Try using a Command Button (ActiveX Control). Use the button caption to identify the toggle state.

Private Sub CommandButton1_Click()

    If CommandButton1.Caption = "-" Then
        ActiveSheet.Outline.ShowLevels Rowlevels:=1, ColumnLevels:=1
        JobDescriptionToggleButton.Caption = "+"
    Else
        ActiveSheet.Outline.ShowLevels Rowlevels:=8, ColumnLevels:=8
        JobDescriptionToggleButton.Caption = "-"
    End If

End Sub

Upvotes: 0

Jill
Jill

Reputation: 19

I tried above answers and it didn't work for me. Below is the code that works:

Sub rowExpanded()

Rows("7:7").Select
Selection.EntireRow.Hidden = IIf(Selection.EntireRow.Hidden, False, True)

End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166980

Sub Macro7()  
    With Rows(7)
        .ShowDetail = Not .ShowDetail
    End With
End Sub

Upvotes: 11

JMK
JMK

Reputation: 28080

Try this

Dim rowExpanded As Boolean
rowExpanded = Rows(7).ShowDetail

If rowExpanded = True Then
    Rows(7).ShowDetail = False
Else
    Rows(7).ShowDetail = True
End If

Upvotes: 1

nutsch
nutsch

Reputation: 5962

No need to. Just adjust your macro to check the current state of your row (collapsed or expanded) and act accordingly:

Sub ExpandOrCollapse()

Rows(7).ShowDetail=IIF(Rows(7).ShowDetail,False,true)

End Sub

Upvotes: 6

Related Questions