Two Combo Boxes Active Control in the same sheet affecting each other

[paazan]

Hi guys,

I have encountered this problem:

I'm trying to put two ComboBoxes in Sheet1, which are supposed to search and return information from two individual lists that are put in Sheet2 and Sheet3, respectively, according to the keyword I key in.

However, they are affecting each other. If I put keywords in ComboBox1 I will get the right return as expected. Then I move on to use ComboBox2, but when I start typing anything into the ComboBox2, while it is still doing the search, I can't see it's returned drop down list, instead, the drop down list of my first search in ComboBox1 will show again. Vise versa, if I start from using ComboBox2 first, the same thing happen when I use ComboBox1.

Here's the codes I use for these two comtrols:


Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "List1"
Me.ComboBox1.DropDown
End Sub


Private Sub ComboBox2_Change()
ComboBox2.ListFillRange = "List2"
Me.ComboBox2.DropDown
End Sub


Please help.


Thanks in advance.

[SamT]

See if this helps

Private Sub ComboBox1_Change()
Application.EnableEvents = False

ComboBox1.ListFillRange = "List1"
Me.ComboBox1.DropDown

Application.EnableEvents = True
End Sub


Private Sub ComboBox2_Change()
Application.EnableEvents = False

ComboBox2.ListFillRange = "List2"
Me.ComboBox2.DropDown

Application.EnableEvents = True
End Sub

[paazan]

Thanks for helping.

Seems the problem still exists.

I have attach the file here. If you use any of the two ComboBoxes to do a search, leave the result without deleting it, and then do another search using another ComboBox, you'll see what happen.

[SamT]

I am using Excel 2002, so I lose some information when I open an xlsm file. Sorry. I can't help. :boohoo

[paazan]

Thank you.

Anyone else please help.

[SamT]

I did notice that "List1" and "List2" are not Ranges. This might be the problem.

Also, you cannot set the initial value of a Control's List with that controls Change sub. There has to be something in the Control to change in order to run the Change sub.



Can you say exactly what you are trying to accomplish.

[paazan]

Hi SamT,

Those two ComboBoxes are supposed to work as search engine, something like Google. As you type in anything, it should detect what you have typed in and give you a list of options to choose from. As you have chosen any of those suggested options, it'll then pull that particular result.
Also those two boxes are placed in the cells that they are linked to. You can move them somewhere else.
Sheet "List1" and "List3" are serving as data base here. The highlighted columns are the ones actually being use. From the left of the highlighted columns, the first one contains the actual data, and the second and third ones work as filters and supposed to change according to what has been entered in the ComboBoxes. The third column would be the final list of options returned to the CoboBoxes in the form of a drop down list for the user to choose best fit result.

[SamT]

ComboBox Lists can be Ranges or Arrays, But not the results of Formulas. The Names in your workbook show that "List1" and "List2" are formulas.


A VBA Function can return an array or a Range Address.

If you want to start with a Listless ComboBox, use the Exit Event sub, then pass the typed in .Value to a Function that returns an Array of the List Items


Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With ComboBox1
If .ListCount = 0 Then
Cancel = True
.List = GetListitems(.Value)
.SetFocus

Else
'do something else
End If
End With

End Sub

Private Function GetListitems(Value_NotUsedIn_ThisDemo) As Variant
GetListitems = Array("Item1", "Item2")
End Function

[paazan]

Both List1 and List2 are defined in Name Manager.

Still need help.

[SamT]

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

The Names in your workbook show that "List1" and "List2" are formulas.

[paazan]

Thanks for your help.

Anyone else have any idea?

The problem now is if I delete one of these two ComboBoxes, only leave one, it works just fine.
When I put two in the same sheet, they kinda interfere each other.

[SamT]

How can somebody help you when

You refuse to listen?

good bye and good luck. Happy coding.

[paazan]

I really have no idea why you started yelling and jumped into the conclusion about me refusing to listen. I meant, basically this is what I'm here for.
Anyway still thank you for your input.

I'm actually trying to build something to use in my work, so really do not have time participate any kind of arguing.
I was simply saying that knowing List1 and List2 having formulas neither answer my question about why two Comboboxes can't be in the same sheet because one single Combobox works just fine, nor did it tell me what to do next to solve the trouble.
My logic is if having not defined range is the cause of the problem, then why one Combobox works?
I do define both of them in formula manager and base on my understanding this way they are both set as range. If I'm wrong, I need some help to make it right, given that this is what causes the problem.

I appreciate your time and patience. And I do hope to see different input as it's been days and the same problem still remains unsolved.