Dynamic combo list linked to a named table, displaying random vendors at bottom of list?

[happy as larry]

Hi

I am setting up a form to select a list of vendors and have used an active x combo box as a list. I have linked that list to a named range "Vendors".

That named range is linked to a table. I have used a table so the list is dynamic, to enable future extension of that list by adding new vendors.

I have used the following formula in the "Vendors" named range =Lists!$B$2:INDEX(Lists!$B:$B,COUNTA(Lists!$B:$B,1))

Note: $B$2 is the 1st cell in the table range, but that cell is also BLANK. Not sure if this is relevant or not, as I know COUNTA generally ignores blanks. However, I need a blank in the drop down so initially the form has blank cells, prior to a user populating.

The issue I have is that the drop down list keeps adding a couple of random Vendor's names at the very bottom of the list (those vendors already exist higher up in the list). It is rather bizarre and I can't fathom why? The random names vary also, if I activate the list and scroll up and down those random names change to 2 other names? Perhaps it is my formula? But all I need to do is have a dynamic drop down, which increases as new vendors are added to the list/table.

Any help would be greatly appreciated.
Many thanks

 

[shknbk2]

I'm not able to repeat your random issue following the code you provided. However, since the named range is linked to a table, how about using this for its formula? Does it help?

=Table1[Column2]

Obviously, you'll have to change the names of the table and the column if they are different.

 

[happy as larry]

Hi. Thanks for your response. Not sure that works as the drop down list is an active X combo box and it will not accept named ranges that way?

 

[shknbk2]

Here's what I have working.

Book1
	B
1	Vendors
2
3	Vendor 1
4	Vendor 2
5	Vendor 3
6	Vendor 4
7	Vendor 5
8	Vendor 6
9	Vendor 7
10	Vendor 8
11	Vendor 9
Sheet1

This is Table1 in the Name Manager pic below. I have also made a named range of "Table1[Vendors]"

Right-click the combobox and choose View Code. This opens the VBE and prepares a blank ComboBox1_Change macro. You don't need this (at least for this part of setting it up.) Instead, put this procedure in its place.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ComboBox1.ListFillRange = Range("Vendors").Address
End Sub

I can add and remove rows in the table, and the combobox is updated accordingly without random entries at the end.

 

[happy as larry]

Thanks for your response, unfortunately that is causing a run-time error?

 

[shknbk2]

I don't know if you followed my steps above or not. I think it should work if you did. The only way I could get your error to show up was to change the code to "Range("Vendors1").Address", where Vendors1 was inserted between the quotes. Since my named range does not have the '1', it gave the same error.

 

[Akuini]

I am setting up a form to select a list of vendors and have used an active x combo box as a list.

Are you talking about active x combobox in a userform or sheet?

 

[happy as larry]

Hi. This is what currently is in my code and I replaced the middle section with your code. Thanks

 

[happy as larry]

Are you talking about active x combobox in a userform or sheet?

Hi. Just standard worksheet, but I have just started to set up a userform also, think that would be better for the end user. Let's see how that pans out !