user form active x radio buttons to hide rows in named range

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854
Can i get a user form active x radio button to hide a row in a named range on a worksheet? or, actually, can those rows not selected be hidden when the user selects one of the options in the frame? each option/radio button has the same name as a row in this named range. So, the radio buttons are "Child", "Adolescent", and "Adult" and the named range "AGE" contains only three rows holding Child, Adolescent, and Adult. if the user selects Adolescent, i want the two other rows to be hidden.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,002
Try this userform module code, changing OptionButton1 etc. to the names of your option buttons.
VBA Code:
Private Sub OptionButton1_Click()
    'Child
    Hide_Rows "2 3"
End Sub

Private Sub OptionButton2_Click()
    'Adolescent
    Hide_Rows "1 3"
End Sub

Private Sub OptionButton3_Click()
    'Adult
    Hide_Rows "1 2"
End Sub

Private Sub Hide_Rows(rowsList As String)

    Dim r As Variant
    
    With Range("AGE")
        .Rows.EntireRow.Hidden = False
        For Each r In Split(rowsList)
            .Rows(r).EntireRow.Hidden = True
        Next
    End With

End Sub
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854
tried it tonight john. i have applied your method to several named ranges. its very slow. any ideas how to speed things up? to get the code to work across the five named ranges, i added a letter to the Hide_Rows macro and the variable for each.

VBA Code:
Private Sub OptionButton1_Click()
    'Child
    Hide_RowsA "2 3"
End Sub

Private Sub OptionButton2_Click()
    'Adolescent
    Hide_RowsA "1 3"
End Sub

Private Sub OptionButton3_Click()
    'Adult
    Hide_RowsA "1 2"
End Sub

Private Sub Hide_RowsA(rowsListA As String)

    Dim a As Variant
    
    With Range("AGE")
        .Rows.EntireRow.Hidden = False
        For Each a In Split(rowsListA)
            .Rows(a).EntireRow.Hidden = True
        Next
    End With
End Sub

Private Sub Hide_RowsB(rowsListB As String)

    Dim b As Variant

    With Range("Site")
        .Rows.EntireRow.Hidden = False
        For Each b In Split(rowsListB)
            .Rows(b).EntireRow.Hidden = True
        Next
    End With
 End Sub
    
Private Sub Hide_RowsC(rowsListC As String)

    Dim c As Variant
    
    With Range("Finance")
        .Rows.EntireRow.Hidden = False
        For Each c In Split(rowsListC)
            .Rows(c).EntireRow.Hidden = True
        Next
    End With
    
End Sub

Private Sub Hide_RowsD(rowsListD As String)

    Dim d As Variant
    
    With Range("Clinical")
        .Rows.EntireRow.Hidden = False
        For Each d In Split(rowsListD)
            .Rows(d).EntireRow.Hidden = True
        Next
    End With
End Sub

Private Sub Hide_RowsE(rowsListE As String)

    Dim e As Variant
    
    With Range("Therapy")
        .Rows.EntireRow.Hidden = False
        For Each e In Split(rowsListE)
            .Rows(e).EntireRow.Hidden = True
        Next
    End With
End Sub


Private Sub OptionButton4_Click()
    'Nundah
    Hide_RowsB "2 3"
End Sub

Private Sub OptionButton5_Click()
    'Telehealth
    Hide_RowsB "1 3"
End Sub

Private Sub OptionButton6_Click()
    'Banyo
    Hide_RowsB "1 2"
End Sub


Private Sub OptionButton7_Click()
    'EAP
    Hide_RowsC "2 3"
End Sub

Private Sub OptionButton8_Click()
    'NDIS
    Hide_RowsC "1 3"
End Sub

Private Sub OptionButton9_Click()
    'Workcover
    Hide_RowsC "1 2"
End Sub
Private Sub OptionButton10_Click()
    'Clin
    Hide_RowsD "2 3 4 5"
End Sub

Private Sub OptionButton11_Click()
    'Counselling
    Hide_RowsD "1 3 4 5"
End Sub

Private Sub OptionButton12_Click()
    'prov Psych
    Hide_RowsD "1 2 4 5"
End Sub
Private Sub OptionButton13_Click()
    'Mental Health
    Hide_RowsD "1 2 3 5"
End Sub

Private Sub OptionButton14_Click()
    'RegPsych
    Hide_RowsD "1 2 3 4"
End Sub

Private Sub OptionButton15_Click()
    'ACT
    Hide_RowsE "2 3 4 5 6 7"
End Sub

Private Sub OptionButton16_Click()
    'CBT
    Hide_RowsE "1 3 4 5 6 7"
End Sub

Private Sub OptionButton17_Click()
    'DBT
    Hide_RowsE "1 2 4 5 6 7"
End Sub
Private Sub OptionButton18_Click()
    'EFT
    Hide_RowsE "1 2 3 5 6 7"
End Sub

Private Sub OptionButton19_Click()
    'EMT
    Hide_RowsE "1 2 3 4 6 7"
End Sub

Private Sub OptionButton20_Click()
    'EMDR
    Hide_RowsE "1 2 3 4 5 7"
End Sub

Private Sub OptionButton21_Click()
    'Schema
    Hide_RowsE "1 2 3 4 5 6"
End Sub
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,002
I don't see how it would be very slow because it's only showing all the rows in the named range and then hiding the specified rows. You could try disabling screen updates, however I doubt it will make much difference.

Also, as your new macros all seem to be doing the same thing but with different named ranges you don't need a separate macro for each named range. Instead, you can have one macro and specify the named range as one of the arguments. These two changes are shown below.
VBA Code:
Private Sub OptionButton1_Click()
    'Child
    Hide_Rows Range("AGE"), "2 3"
End Sub

Private Sub OptionButton2_Click()
    'Adolescent
    Hide_Rows Range("AGE"), "1 3"
End Sub

Private Sub OptionButton3_Click()
    'Adult
    Hide_Rows Range("AGE"), "1 2"
End Sub

Private Sub Hide_Rows(namedRange As Range, rowsList As String)

    Dim r As Variant
    
    Application.ScreenUpdating = False
    With namedRange
        .Rows.EntireRow.Hidden = False
        For Each r In Split(rowsList)
            .Rows(r).EntireRow.Hidden = True
        Next
    End With
    Application.ScreenUpdating = True

End Sub
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854
That will tidy things up. not sure why its so slow. there are five named ranges (three with three rows, one with five rows and another with seven rows). the data set is only 19 columns wide and consists of a check mark in some cells, not in others. Its a skills matrix. got me stumped
 

Forum statistics

Threads
1,141,413
Messages
5,706,300
Members
421,440
Latest member
cmphares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top