Dynamically select List Object name

I have a workbook with three sheets:
-"DB" database sheet containing multiple tables (20 to be exact, named as "CityA", "CityB", etc.)

-"Threat Data" reference sheet containing a "City_Ref" table with the list of tables names in the "DB City ID" column and the unique city name assigned to each table in the "City" column (the city name is populated in a "City" column of each table in the database sheet).

-a dashboard sheet containing an interactive userform for which to populate the database tables.

In this userform, there are two comboboxes:
-a "CbxCity" combobox which lists all the city names from a "City" column in the "City_Ref" reference table

- a "CbxAsset" combobox which should automatically list all the values in the "Asset" column of the selected city table based on "CbxCity"

My issue lies in filling "CbxAsset" based on the selection in "CbxCity" ; how do I dynamically select a ListObject name based on a selection?

The code I am working with is:

Please Login or Register  to view this content.

In the CbxCity_Change() sub, I am not sure how to name the ListObject and my code currently gets an error at r = Me.CbxCity.Value
which is strange because that is showing the selected city name when I run the cursor over the bug.

Any help is greatly appreciated, please inform if my problem doe not make sense! Due to the size and confidential nature of my workbook, I am unable to post it here.

To further clarify my problem, instead of directly naming a ListObject such as: WorkSheets("Sheet1").ListObjects("Table1")
how do you set the ListObject variable to the selected table when there are many from which the user must first choose?

Any suggestions? I am sure there is a relatively straight forward solution but I cant seem to get my head around it..

@Norie, thanks for your reply however the user will not enter a table name into the first combobox since it should be populated with all the tables from which to choose. The second combobox must then list all the values in the second column of the selected table from the first combobox.

Yes they will select a table in the first combobox, however they will not type in a name.

Right of course, silly mistake. I will re-declare r, however you are correct in that the first combobox doesnt currently have the tables names but rather the city names associated to each table. This is another more minor issue I am working out, because the table names are "CityA","CityB", etc (20 in total, listed in the first column of a CityID reference table), but I want the combobox to show the tables' city names (listed in the second column of the CityID reference table).

I could simply have the combobox contain two columns: the first one listing the actual table names and the second listing the City names. Then I could just minimize the first column in the combobox to hide it so that the user only sees the second column with the city names, and then just use the value of the first column for referencing the selected table.

Does this make sense? I am currently waiting to play around with the code but my file is frozen!! (its a big file)

So I have been trying to run the code with the first combobox simply set at listing the actual table names however still cannot seem to get the ListObject name to be variable on a value defined by the first combobox selection:

Please Login or Register  to view this content.

Similarly, the following version will not work:

Please Login or Register  to view this content.

Any suggestions? I have seen a sample workbook from a colleague who successfully used the first method of concatenating the name and combobox ListIndex reference number- however it was used for naming the ListColumn and not ListObject. For some reason it does not work the other way around..

The ListIndex in the first code simply returns the values in the "CbxCity" combobox as a number (starting at 1) which is then concatenated with "City" to yield the table name since the table names are "City1", "City2" etc.

I have created a very rough sample Muli-Table Sample.xlsm , which amazingly works with both methods of the code above. For some reason it is not working in my workbook; returning a "Subscript out of range" bug at the following line in both methods:

Please Login or Register  to view this content.

I will keep tinkering and post any updates.

I actually just did that and found I had the ListColumn name incorrectly (it is "Assets" in my actual workbook). Luckily I am too tired to want to break something but at least my issue is finally resolved!!

Thanks for your help Norie.

I actually just did that and found I had the ListColumn name incorrectly (it is "Assets" in my actual workbook). Luckily I am too tired to want to break something but at least my issue is finally resolved!!

Thanks for your help Norie.