Solved: Dynamic Combobox in a userform

[WebGuy]

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]

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]

Great! Please post a good example.

[ALe]

[VBA]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[/VBA]

[WebGuy]

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]

If you mean a reference to Range("a1") of Sheet1
[VBA]me.combobox2.additem worksheets("Sheet1").Range("a1").value[/VBA]

[WebGuy]

Thanks alot ! It all works now !

[ALe]

you welcome! mark this thread as solved please

[debauch]

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.
[vba]
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
[/vba]