PDA

View Full Version : Solved: Dynamic Combobox in a userform



WebGuy
07-06-2006, 04:34 AM
Hi !

I have a problem making comboboxes dynamic in my userform.
I want to make combobox2 dependant on which value i choose in combobox1. I guess this is easiest illustrated with an example.

combobox1 has the values:
A
B
C

Depending on which option i choose in combobox1 i want combobox2 to display different values in the dorpdown menu.

Combobox2:

A B C
1 2 3
4 5 6
7 8 9

If i chose A in combobox 1 i can choose 1,4 or 7 in combobox2.
I can't really figure out how to make this work. I have checked books and the knowledge database without finding any satisfactory sollutions.

debauch
07-06-2006, 04:47 AM
I have lots of example of this on my other station. If you do not get a response you are looking for, I can post them later today.

WebGuy
07-06-2006, 05:08 AM
Great! Please post a good example.:clap:

ALe
07-06-2006, 05:26 AM
Private Sub ComboBox1_Change()
Myval = Me.ComboBox1.Value
Me.ComboBox2.Clear
Select Case Myval
Case "A"
Me.ComboBox2.AddItem "1"
Me.ComboBox2.AddItem "4"
Me.ComboBox2.AddItem "7"
Case "B"
Me.ComboBox2.AddItem "2"
Me.ComboBox2.AddItem "5"
Me.ComboBox2.AddItem "8"
Case "C"
Me.ComboBox2.AddItem "3"
Me.ComboBox2.AddItem "6"
Me.ComboBox2.AddItem "9"
End Select
End Sub
Private Sub UserForm_Activate()
With Me.ComboBox1
.AddItem "A"
.AddItem "B"
.AddItem "C"
End With
End Sub

WebGuy
07-06-2006, 06:25 AM
Great! It works just like i would like it to. But how do i make the reference for combobox2 be "HW.Ref.Sheet!A1" instead of "A"?

ALe
07-06-2006, 06:35 AM
If you mean a reference to Range("a1") of Sheet1
me.combobox2.additem worksheets("Sheet1").Range("a1").value

WebGuy
07-06-2006, 07:05 AM
Thanks alot ! It all works now !
:beerchug:

ALe
07-06-2006, 07:14 AM
you welcome! mark this thread as solved please

debauch
07-06-2006, 05:15 PM
Hello,
Attached is an alternate solution I often use. It is a combination of vlookups, w/ comboboxes. It makes updating dropdown info easy, for you or others who may use the program.

Private Sub UserForm_Initialize()
'on form load
TextBox1.Text = "Please select an option from the drop-down menu"
'add excel sheet to comobobox
With ComboBox1
.RowSource = ("verbatims!a1:a10") 'inital selection
ComboBox1.SetFocus
End With

End Sub

Private Sub ComboBox1_Change()
'when something is selected
On Error GoTo Err_Handler

'verbatims
If ComboBox1.Text = "" Then
'MsgBox ("Please Select Valid Type")
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""

ComboBox1.SetFocus

Else

'these are vlookups in a hidden excel sheet, to easily update
'your data, or if others would need to update it easily
Range("calculations!a1") = ComboBox1.Value
TextBox1.Value = Range("calculations!b1")
TextBox2.Value = Range("calculations!c1")
TextBox3.Value = Range("calculations!d1")


Exit Sub
Err_Handler:
ComboBox1.Value = ""
MsgBox "Invalid Entry!", vbCritical, "Warning!"
End If
End Sub