Pookiemeister
Well-known Member
- Joined
- Jan 6, 2012
- Messages
- 563
- Office Version
- 365
- 2010
- Platform
- Windows
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:
The error occurs on variable "str" in BOLD FONT:
However when I remove the line that says
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
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
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
Code:
Me.cmbPrdCde.Clear
I really hope this explanation is easily understood.
Thank You