Why are my option buttons not switching up combo box text?

user_12080212 0
Viewed 27

I have two option buttons that will allow a user to either search a job by it's # or it's name. enter image description here

so if you click the left opt button, it'll display as:

enter image description here

and then vice-versa:

enter image description here

So then I added this to populate the lists:

Sub Search_Reqs_Cmb_IM()
    Dim rWs As Worksheet: Set rWs = ThisWorkbook.Worksheets("Approved Roles")

    If UserForm2.optSearchReq.Value = True Then 'search by job #
        For x = 2 To rWs.Cells(Rows.Count, 2).End(xlUp).row
            UserForm2.cmbReqNum.AddItem rWs.Cells(x, 2) & " - " & rWs.Cells(x, 13)
        Next x
    ElseIf UserForm2.optSearchJob.Value = True Then 'search by job name
        For x = 2 To rWs.Cells(Rows.Count, 2).End(xlUp).row
            UserForm2.cmbReqNum.AddItem rWs.Cells(x, 13) & " - " & rWs.Cells(x, 2)
        Next x
    End If
End Sub

and then viewed code for each option button to call Search_Reqs_Cmb_IM each time a button is clicked.

So when I click on either one, it will always populate that no matter if I toggle between the two option buttons. I want it to switch to job name - job number if I click on that opt button and vice versa. How can I make sure it'll switch between the two formats?

vba excel
1 Answers

You need to call .Clear before .AddItem otherwise your list will just grow every time the option is selected.

Sub Search_Reqs_Cmb_IM()
    Dim rWs As Worksheet: Set rWs = ThisWorkbook.Worksheets("Approved Roles")

    If UserForm2.optSearchReq.Value = True Then 'search by job #
        UserForm2.cmbReqNum.Clear
        For x = 2 To rWs.Cells(Rows.Count, 2).End(xlUp).Row
            UserForm2.cmbReqNum.AddItem rWs.Cells(x, 2) & " - " & rWs.Cells(x, 13)
        Next x
    ElseIf UserForm2.optSearchJob.Value = True Then 'search by job name
        UserForm2.cmbReqNum.Clear
        For x = 2 To rWs.Cells(Rows.Count, 2).End(xlUp).Row
            UserForm2.cmbReqNum.AddItem rWs.Cells(x, 13) & " - " & rWs.Cells(x, 2)
        Next x
    End If
End Sub
user_81738700
Related