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

happy as larry

New Member
Jan 18, 2018
Office Version
  1. 2021
  1. Windows

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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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?
Excel Formula:
Obviously, you'll have to change the names of the table and the column if they are different.
Upvote 0
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?
Upvote 0
Here's what I have working.

3Vendor 1
4Vendor 2
5Vendor 3
6Vendor 4
7Vendor 5
8Vendor 6
9Vendor 7
10Vendor 8
11Vendor 9

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.

VBA Code:
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.


  • NameManager.png
    5.3 KB · Views: 6
Upvote 0
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.
Upvote 0
Hi. This is what currently is in my code and I replaced the middle section with your code. Thanks

Upvote 0
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 ! :)
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back