Combobox questions

[Mekinnik]

I would like to know what events I should use or if I should use an event for
the following questions?

1)How to capture what the user input(types) into the combobox1 and see if it
matches any names in say sheet1 column 'A'. If it does then give combobox2
focus.

2)Populate combobox2's list with names that match the name the user typed
into combobox1 from sheet2 column 'B' and continue with the entry form input.
Sheet2 looks like this. So if the user typed "john" combobox2's list would
show red,blue,brown.
column A column B
john red
fred red
john blue
jack green
john brown

3)If the user selects one of the names from combobox1 list I would like it
to do the same as item #2 above.

column 'A' from both sheets match, they just store different data

 

[Chip Pearson]

Try code for ComboBox1 like the following. Change the ranges A1:A10 to
whatever you need.

Private Sub ComboBox1_Change()
Dim S As String
Dim V As Variant
Dim R As Range

S = Me.ComboBox1.Text
V = Application.Match(S, Worksheets("Sheet1").Range("A1:A10"), 0)
If IsError(V) = False Then
With Me.ComboBox2
.Clear
For Each R In Worksheets("Sheet2").Range("A1:A10")
If R.Text = S Then
.AddItem R(1, 2)
End If
Next R
.SetFocus
If .ListCount > 0 Then
.ListIndex = 0
End If
End With
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)