AnnieM
01-25-2017, 06:30 AM
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
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