ComboBox change for multiple comboboxes

[AnnieM]

Hi Experts,

I am hoping that someone can assist with the following to improve the VBA code that I have:

My Userform has a combobox_change event that is also dependant on an option button state. The outcome of the change event / option button state determines the content of another text box.

The (sanitized) code I have is:


Private Sub ComboBox1_Change()
If OptionButton1 = True And ComboBox1.Value = "Widget" Then
TextBox1.Value = TextBoxWidget
Else
If OptionButton1 = True And ComboBox1.Value = "Gizmo" Then
TextBox1.Value = TextBoxGizmo
Else
If OptionButton1 = True And ComboBox1.Value = "Item A" Or OptionButton1 = True And ComboBox1.Value = "Item B" Or _
OptionButton1 = True And ComboBox1.Value = "Item C" Or OptionButton1 = True And ComboBox1.Value = "Item D" Or _
OptionButton1 = True And ComboBox1.Value = "Item E" Then
TextBox1.Value = TextBoxA-E
Else
If OptionButton1 = True And ComboBox1.Value = "F" Or OptionButton1 = True And ComboBox1.Value = "G" Then
TextBox1.Value = TextBoxG-H
Else
If OptionButton1 = True And ComboBox1.Value = "I" Then
TextBox1.Value = TextBoxI
Else
If OptionButton1 = True And ComboBox1.Value = "J" Then
TextBox1.Value = TextBoxJ
Else: TextBox1.Value = ""
End If
End If
End If
End If
End If
End If
End Sub

Although the above works fine, I am wondering if there is a more elegant way of handling this?

Also, I have to repeat the exact same code above for ComboBox2 -12 and the corresponding TextBox2 -12 and wonder if anyone can suggest a better solution than simply repeating the above a further 11 times?


Many thanks,

Anne

[offthelip]

A vary easy way to simplify this is to put an if statement round the whole lot to take out all the multiple "If optionButton1 = TRUE" tests.
To make it simpler to edit for your 12 copies I would do it like this:
Private Sub ComboBox1_Change()
Dim teststrings(1 To 11) As String
Dim CBvalue As String


teststrings(1) = "Widget"
teststrings(2) = "Gizmo"
teststrings(3) = "Item A"
teststrings(4) = "Item B"
teststrings(5) = "Item C"
teststrings(6) = "Item D"
teststrings(7) = "F"
teststrings(8) = "G"
teststrings(9) = "H"
teststrings(10) = "I"
teststrings(11) = "J"
If OptionButton1 = True Then
TextBox1.Value = ""
CBvalue = Combobox1.Value
foundf = False
For i = 1 To 11
If CBvalue = teststrings(i) Then
foundf = True
Exit For
End If
Next i

If foundf Then
Select Case i

Case 1


TextBox1.Value = TextBoxWidget
Case 2
TextBox1.Value = TextBoxGizmo
Case 3, 4, 5, 6
TextBox1.Value = TextBoxA - E
Case 7, 8, 9, 10
TextBox1.Value = TextBoxG - H
Case 11
TextBox1.Value = TextBoxI
Case 12
TextBox1.Value = TextBoxJ
Case Else
TextBox1.Value = ""
End Select
End If
End If
End Sub





:Note I haven't tested this so there could be errors, just trying to show you how I would do it

[Kenneth Hobs]

I leave 2-12 to you.

I guessed that TextBoxA-E meant sustract the values from TextBoxA and TextBoxB since "-" is not a valid control name character.

Private Sub ComboBox1_Change()
cbChange i
End Sub


Private Sub cbChange(i%)
Dim c As ComboBox, o As OptionButton, t As TextBox

Set o = Controls("OptionButton" & i)
If o = False Then Exit Sub

Set c = Controls("ComboBox" & i)
Set t = Controls("TextBox" & i)

Select Case True
Case c = "Widget"
t = textboxwidget
Case c = "Gizmo"
t = textboxgizmo
Case c = "Item A", c = "Item B", c = "Item C", c = "Item D", c = "Item E"
t = Val(TextboxA) - Val(TextboxE)
Case c = "F", c = "G"
t = Val(TextboxG) - Val(TextboxH)
Case c = "I"
t = TextboxI
Case c = "J"
t = TextboxJ
Case Else
End Select
End Sub

[AnnieM]

Hi,

Many thanks for your response, greatly appreciated.

I have tried your solution but I am getting a few errors (re:declaring variables). I am sure that I can fix that though and will see if I can get this to work.

I think it will make the rest of the comboboxes easier to code for.

Regards,

Anne

[AnnieM]

Hi Kenneth,

Apologies, I misled everyone. I used 'TextBoxA-E' instead of the real control to preserve confidentiality.

I will go through the solution you've provided though and see if I can apply this.

Thanks for your response,

Anne