Forum Discussion

MrPet88's avatar
MrPet88
Copper Contributor
Jul 30, 2024
Solved

Hide/unhide rows using a button

Hello everyone.

 

I am trying to configure a button to hide/unhide several rows at a time.

I am following these steps:

 

1) Adding a toggle button in the Active X control

 

 

2) Then I double click in the button, and paste the following code:

 

Private Sub ToggleButton1_Click()
    Dim xAddress As String
    Dim splitAddress As Variant
    xAddress = "4:7"
    splitAddress = Split(xAddress, ",")

    If ToggleButton1.Value Then
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = True
            ToggleButton1.Caption = "+"
        Next Var

    Else
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = False
            ToggleButton1.Caption = "-"
        Next Var
        End If
End Sub

 

3) I insert a new button following the same procedure, double click it and add code for the new button, with a different set of rows. Now it looks like this:

 

 

Private Sub ToggleButton1_Click()
    Dim xAddress As String
    Dim splitAddress As Variant
    xAddress = "4:7"
    splitAddress = Split(xAddress, ",")

    If ToggleButton1.Value Then
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = True
            ToggleButton1.Caption = "+"
        Next Var

    Else
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = False
            ToggleButton1.Caption = "-"
        Next Var
        End If
End Sub


Private Sub ToggleButton2_Click()
 Dim xAddress As String
    Dim splitAddress As Variant
    xAddress = "9:14"
    splitAddress = Split(xAddress, ",")

    If ToggleButton1.Value Then
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = True
            ToggleButton1.Caption = "+"
        Next Var

    Else
        For Each Var In splitAddress
            Application.ActiveSheet.Rows(Var).Hidden = False
            ToggleButton1.Caption = "-"
        Next Var
        End If
End Sub

 

 

The first toggle button is working fine, but the second is not, even when is the same code.

What am I doing wrong here?

Thanks.

  • Hi, try changing the reference in the button 2. as i see, the button 2 is referencing to the button1 in your code.

    Private Sub ToggleButton1_Click()
    Dim xAddress As String
    Dim splitAddress As Variant
    Dim btn As ToggleButton

    xAddress = "68:72"
    splitAddress = Split(xAddress, ",")

    If ToggleButton1.Value Then
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = True
    ToggleButton1.Caption = "+"
    Next Var

    Else
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = False
    ToggleButton1.Caption = "-"
    Next Var
    End If
    End Sub

    Private Sub ToggleButton2_Click()
    Dim xAddress As String
    Dim splitAddress As Variant
    xAddress = "80:90"
    splitAddress = Split(xAddress, ",")

    If ToggleButton2.Value Then
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = True
    ToggleButton2.Caption = "+"
    Next Var

    Else
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = False
    ToggleButton2.Caption = "-"
    Next Var
    End If
    End Sub

     

    this works for me. I dont know if this is what you wanted to do

1 Reply

  • GopiBalaraju's avatar
    GopiBalaraju
    Copper Contributor

    Hi, try changing the reference in the button 2. as i see, the button 2 is referencing to the button1 in your code.

    Private Sub ToggleButton1_Click()
    Dim xAddress As String
    Dim splitAddress As Variant
    Dim btn As ToggleButton

    xAddress = "68:72"
    splitAddress = Split(xAddress, ",")

    If ToggleButton1.Value Then
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = True
    ToggleButton1.Caption = "+"
    Next Var

    Else
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = False
    ToggleButton1.Caption = "-"
    Next Var
    End If
    End Sub

    Private Sub ToggleButton2_Click()
    Dim xAddress As String
    Dim splitAddress As Variant
    xAddress = "80:90"
    splitAddress = Split(xAddress, ",")

    If ToggleButton2.Value Then
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = True
    ToggleButton2.Caption = "+"
    Next Var

    Else
    For Each Var In splitAddress
    Application.ActiveSheet.Rows(Var).Hidden = False
    ToggleButton2.Caption = "-"
    Next Var
    End If
    End Sub

     

    this works for me. I dont know if this is what you wanted to do

Resources