Combobox questions

M

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
 
C

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top