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

Pookiemeister

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010
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:
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:
Code:
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
Code:
    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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,086
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try adding code shown in RED & see if solves your problem

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,990
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top