Can't use more than one combo box on excel

0
votes

I have made two active X combo boxes for a list of 220 sites. Before making the active x combo box i have made a sheet that searches through my data and finds the sites that match with what i am typing as i go.

I then used the name manage, refering to the formula in the first cell of the list 

=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$220,COUNTIF(Sheet1!$G$2:$G$220,"?*"))

I have then writen this in the ListFillRange in the properties of my combo box.

It works fine for one, but once i had made the second one and selected the site the first one will no longer let me search through. I have used the same formulas on both but they originate from different sheets to see if this fixed the problem however that was unsuccessful. (the boxes are on different sheets) When i click on the next sheet after using the box on the first sheet, it still shows part of it as if it is crashing. The boxes are independent so I'm not sure what to do next as i need to add another 3 on separate sheets. I am also using this code for each box 

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

and similar for the other button but with a different range.

1
excelvbalistcomboboxdynamic-list
please post code behind comboboxes, since you used the vba tag. If no vba is involved remove that tag. – Scott Holtzman
try using different names for you Combo Boxes (ComboBox_1 and ComboBox_2 for example). – Oscar Anthony
thanks but I've tried that and it doent solve it. – Josie
Can you try omitting the Me.ComboBox1.DropDown ? – DukeOfHazard
Tried that too! Sadly didn't make any difference – Josie

1 Answers

1
votes

There is no need to use VBA for this, the Change Events specifically, if you just want to use and fill the combo boxes with Named Ranges.

The scenario I think you try to do is:

  • Create Named Ranges that will be the source of your combobox: Fill the range with your data, select the range, Right Click, Select Define Name and give the range a name. MList in your case I believe.
  • Create Combobox: Goto Developer Tab, Insert in your case ActiveX ComboBox, Draw it on your sheet, right click the ComboBox, select properties, find ListFillRange in properties and enter the name of the Named Range you created in step one
  • Repeat for Combobox 2, with the same or a different Named Range depending on what you try to do
  • Leave Design Mode

Boths Comboboxes can now be used to type in what you are looking for.

If this is not what you tried to do, please try edit your question and in detail try to explain what you try to do and what you like to accomplish by doing so.