how to clear contents displayed in combobox when option button is changed

[Pookiemeister]

I have six option buttons and a combo box on a form. Depending on which option button is selected would determine what gets populated inside the combobox. All combobox values for each option button pulls values from a spreadsheet. As of now, if the user accidentally selects the wrong option button and selects the product from that combobox and realizes that they made a mistake and chooses the correct option button I would like the value in that combo box to disappear when the new option box is selected.
The problem is the values that are inside the combobox are concatenated from two cells. There is also another section of code that uses the value from the combobox and un-concatenate those values and that is where the error occurs. I am getting runtime error number nine subscript out of range.
Below is the entire code:

Private Sub optIMA_Change()
    Me.cmbPrdCde.Clear
    With ThisWorkbook.Worksheets("Product_Info")
        ary = .Range("M3", .Range("M" & Rows.Count).End(xlUp).Offset(, 1))
        ReDim nary(1 To UBound(ary))
            For R = 1 To UBound(ary)
                nary(R) = ary(R, 1) & " (" & ary(R, 2) & ")"
                'Debug.Print nary(r)
            Next R
        Me.cmbPrdCde.List = nary
    End With
End Sub


Private Sub optKorber_Change()
    Me.cmbPrdCde.Clear
    With ThisWorkbook.Worksheets("Product_Info")
        ary = .Range("I3", .Range("I" & Rows.Count).End(xlUp).Offset(, 1))
        ReDim nary(1 To UBound(ary))
            For R = 1 To UBound(ary)
                nary(R) = ary(R, 1) & " (" & ary(R, 2) & ")"
                'Debug.Print nary(r)
            Next R
        Me.cmbPrdCde.List = nary
    End With
End Sub
Private Sub optSlat_Change()
    Me.cmbPrdCde.Clear
    With ThisWorkbook.Worksheets("Product_Info")
        ary = .Range("A3", .Range("A" & Rows.Count).End(xlUp).Offset(, 1))
        ReDim nary(1 To UBound(ary))
            For R = 1 To UBound(ary)
                nary(R) = ary(R, 1) & " (" & ary(R, 2) & ")"
                'Debug.Print nary(r)
            Next R
        Me.cmbPrdCde.List = nary
    End With
End Sub
Private Sub optUhlmann2_Change()
    Me.cmbPrdCde.Clear
    With ThisWorkbook.Worksheets("Product_Info")
        ary = .Range("E3", .Range("E" & Rows.Count).End(xlUp).Offset(, 1))
        ReDim nary(1 To UBound(ary))
            For R = 1 To UBound(ary)
                nary(R) = ary(R, 1) & " (" & ary(R, 2) & ")"
                'Debug.Print nary(r)
            Next R
        Me.cmbPrdCde.List = nary
    End With
    
End Sub
Private Sub optUhlmann5_Change()
    Me.cmbPrdCde.Clear
    With ThisWorkbook.Worksheets("Product_Info")
        ary = .Range("Q3", .Range("Q" & Rows.Count).End(xlUp).Offset(, 1))
        ReDim nary(1 To UBound(ary))
            For R = 1 To UBound(ary)
                nary(R) = ary(R, 1) & " (" & ary(R, 2) & ")"
                'Debug.Print nary(r)
            Next R
        Me.cmbPrdCde.List = nary
    End With
End Sub
Private Sub optPouch_Change()
    Me.cmbPrdCde.Clear
    With ThisWorkbook.Worksheets("Product_Info")
        ary = .Range("U3", .Range("U" & Rows.Count).End(xlUp).Offset(, 1))
        ReDim nary(1 To UBound(ary))
            For R = 1 To UBound(ary)
                nary(R) = ary(R, 1) & " (" & ary(R, 2) & ")"
                'Debug.Print nary(r)
            Next R
        Me.cmbPrdCde.List = nary
    End With
End Sub

The error occurs on variable "str" in BOLD FONT:

Private Sub cmbPrdCde_Change()
        
    [B]str [/B]= Me.cmbPrdCde.Value
    str1 = Replace(Split(str, "(")(1), ")", vbNullString)
    str = Replace(Split(str, "(")(0), ")", vbNullString)
    str = Replace(str, " ", "")
    
End Sub

However when I remove the line that says

    Me.cmbPrdCde.Clear

the error goes away. Although the old value is left in the combobox but when the user selects the combobox the new content is displayed and can be selected. The sole purpose of the Me.cmbPrdCde.Clear is to make it look more professional. Which I am not.
I really hope this explanation is easily understood.

Thank You

 

[dmt32]

Hi,
try adding code shown in RED & see if solves your problem

Private Sub cmbPrdCde_Change()
     If Me.cmbPrdCde.ListIndex <> -1 Then
        Str = Me.cmbPrdCde.Value
        str1 = Replace(Split(Str, "(")(1), ")", vbNullString)
        Str = Replace(Split(Str, "(")(0), ")", vbNullString)
        Str = Replace(Str, " ", "")
    End If
End Sub

Just an observation, you are repeating the same code in each optionbutton with only a change to the Column reference.
You could Consider making a common code & passing the required column to it.

Dave