Active X Combobox Dropdown Issues

[quick_question]

I currently have 2 separate comboboxes pointing to separate named lists.

I added some vba to create an autocomplete dropdown.

The code points to the correct combobox and references the correct list for each combobox.

My problem is that when I type in the first one, select a value from the options that populate - then go to the next combobox and begin typing...the first combobox dropdown appears as if I were typing in that combobox.

The first combobox (the first one I type in) is in column B and the second combobox is in column E.

Why is this happening??

Any thoughts or suggestions as to how I can fix this problem is greatly appreciated!!

 

[Russell Hauf]

Re: Active X Combobox Dropdown Issues - PLEASE HELP!!!!

I suggest posting the code behind both comboboxes. The more information you give, the more help you'll get. : )

 

[quick_question]

Re: Active X Combobox Dropdown Issues - PLEASE HELP!!!!

Comboboxes are on sheet1

vba for the comboboxes is:

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "JobNameList"
Me.ComboBox1.DropDown
End Sub
-----------------------------------------------------------------------
Private Sub ComboBox2_Change()
ComboBox2.ListFillRange = "DistNameList"
Me.ComboBox2.DropDown
End Sub

Named Lists are on sheet2, DistNameList is in column E and JobNameList is in column K.

Columns A-D on sheet 2 are;
A & B - pivot table that points to another file which is a repository file for data.
C has a formula that calculates if what is typed on sheet1 is found in the row to the left ---->> =--ISNUMBER(IFERROR(SEARCH(PO!$B$20,A257,1),""))
D has a formula that identifies which rows have matching data ---->> =IF(C257=1,COUNTIF(C$257:$C257,1),"")
E has a formula that arranges column D results by alphabetical order ---->> =IFERROR(INDEX(A$257:A$1000,MATCH(ROWS(D$257:D257),D$257:D$1000,0)),"")

There are currently only about 15 rows of data, but as the underlying data builds which feeds the pivot table, columns A & B will grow.

The same progression is found in columns G-J


Combobox properties:

file:///C:/Users/Ryan%20D%20McDonough/Desktop/Combobox_Properties1.JPG

file:///C:/Users/Ryan%20D%20McDonough/Desktop/Combobox_Properties2.JPG

 

[Russell Hauf]

Re: Active X Combobox Dropdown Issues - PLEASE HELP!!!!

Do you need the .DropDown code in the change event? Won't the combo already be visible when there is a change? Does JobNameList have any dependencies on DistNameList (or vice versa)? If so, this may trigger a change in the ComboBox, thereby triggering your .DropDown code.

 

[quick_question]

Re: Active X Combobox Dropdown Issues - PLEASE HELP!!!!

I'm not well versed in macros - just well enough that I can search and find what I'm trying to implement and maybe change them around to better suit my needs.

I tried to remove the .Dropdown and the dropdown function did go away - I'm looking to keep that if possible.

Neither list is dependent on the other.

They are pulled from two separate pivot tables, which do pull from the same source data set.

The 2 pivot tables are titled differently; PivotTable1 & PivotTable2.

 

[Russell Hauf]

Re: Active X Combobox Dropdown Issues - PLEASE HELP!!!!

Not sure what you mean by, the function went away. Without more info, I'm not sure what to say here. What are you looking to keep that you lost?

 

[quick_question]

Re: Active X Combobox Dropdown Issues - PLEASE HELP!!!!

Prior to removing .dropdown, if I typed "a" (into the combobox) a list of all matches that started with "a" would be listed below the combobox as if I had clicked an arrow in a dropdown to see the choices...but this was automatic as I typed.

When I removed the .dropdown - this was the function that I lost.

I'm looking to keep this function, as I type - automatically show options in a dropdown list.

 

[Russell Hauf]

Try putting the .DropDown into the...I believe it is the _Enter event, instead of the Change evwnt.