Populate combobox with different dynamic tables

Rulle

New Member
Joined
Nov 9, 2018
Messages
7
I have a workbook with a lot of data.
The data is in about 100 different, 1 collumn tables with unique names.

Then i have different userforms with comboboxes in it.
I want to populate the comboboxes with the data from the different tables, but without referring to sheetnames.
In this way I can move the tables to different sheets without affecting the code in the userforms.

This seems to be easy as I just fill rowsource with e.g “table1” in the property window. But in some comboboxes I need to populate all the data from table1 and only some data from table2, lets say column 2,3 and 4.


Have tried a lot of ways and are a little stuck now, so I really hope someone have the answer.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I have a workbook with a lot of data.
The data is in about 100 different, 1 collumn tables with unique names.

Then i have different userforms with comboboxes in it.
I want to populate the comboboxes with the data from the different tables, but without referring to sheetnames.
In this way I can move the tables to different sheets without affecting the code in the userforms.

This seems to be easy as I just fill rowsource with e.g “table1” in the property window. But in some comboboxes I need to populate all the data from table1 and only some data from table2, lets say column 2,3 and 4.


Have tried a lot of ways and are a little stuck now, so I really hope someone have the answer.

Actually Excel creates a named range for a table (DataBodyRange part) with the same name as the table. You can see it in the name manager.
So you can populate the comboboxes like this:

Code:
Private Sub UserForm_Initialize()
ComboBox1.List = Application.Range("Table1").Value
End Sub


So when you move the table to another sheet the address will change accordingly.
But there could be a problem with this approach, if you have another workbook open and it is the active workbook and it has table with the same name then the code will refer to this table (not to the one you intended).

But in some comboboxes I need to populate all the data from table1 and only some data from table2, lets say column 2,3 and 4.
Can you explain more? maybe by posting some example.
When you add data from another table you mean add the column (in the combobox) or just add the row in the existing column.

To populate the combobox from multiple range I think you need to populate the ranges to an array then populate the combobox from that array. We can't use Union to do that.
 
Upvote 0
Can you explain more? maybe by posting some example.
When you add data from another table you mean add the column (in the combobox) or just add the row in the existing column.

To populate the combobox from multiple range I think you need to populate the ranges to an array then populate the combobox from that array. We can't use Union to do that.

Sure. in this example i have 2 tables with different values, and unique names. Table1 and table2.

https://imgur.com/MwI2RUy

Then i have a combobox in the userform, where i want to populate the combobox with all the data from table1 and only the 3 first cells from table2.("blank", "cobber" and "Anthra"). But still in a dynamic way, where i can move the table without affecting the code.

So when you push the combobox, it will give you these choices:
white, green, black, yellow, blue, grey, orange, write your own color, blank, cobber and anthra.

Cant figure out how to do this, but it sounds like the right way to approach with arrays. Can you show an example?
 
Upvote 0
You have 2 tables Tabel1 and Tabe2
In Tabel2 create a defined name range and call it Part1
Put this code in the UserForm_Initialize event
(name of the combobox is ComboBox1
Code:
Private Sub UserForm_Initialize()
Set rng1 = [Tabel1]
Set rng2 = [Part1]
    For Each cl In rng1
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
     
     For Each cl In rng2
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
     
     ComboBox1.List = Split(arStr, ",") '
End Sub
Working example here
https://we.tl/t-9frKbboq0G
 
Upvote 0
You have 2 tables Tabel1 and Tabe2
In Tabel2 create a defined name range and call it Part1
Put this code in the UserForm_Initialize event
(name of the combobox is ComboBox1
Code:
Private Sub UserForm_Initialize()
Set rng1 = [Tabel1]
Set rng2 = [Part1]
    For Each cl In rng1
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
     
     For Each cl In rng2
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
     
     ComboBox1.List = Split(arStr, ",") '
End Sub
Working example here
https://we.tl/t-9frKbboq0G


Thank you

works perfectly!!!

Can you descibe what it is you are doing?

if arst = "". How can it be "", when the value is not defined?

Or link me to a page where i can learn the meaning behind the code, so i undertand it.
 
Upvote 0
Sure. in this example i have 2 tables with different values, and unique names. Table1 and table2.

https://imgur.com/MwI2RUy

Then i have a combobox in the userform, where i want to populate the combobox with all the data from table1 and only the 3 first cells from table2.("blank", "cobber" and "Anthra"). But still in a dynamic way, where i can move the table without affecting the code.

So when you push the combobox, it will give you these choices:
white, green, black, yellow, blue, grey, orange, write your own color, blank, cobber and anthra.

Cant figure out how to do this, but it sounds like the right way to approach with arrays. Can you show an example?
Both table only have one column, right?
Since you're dealing with a single column table & a single column combobox & also small data we can just use Additem method.
Like this:

Code:
Private Sub UserForm_Initialize()
Dim c As Range
ComboBox1.List = Application.Range("Table1").Value

For Each c In Application.Range("Table2").Resize(3, 1)
    ComboBox1.AddItem c.Value
Next

End Sub


Note: I use Resize(3, 1), it means we only use the first 3 cell data in Table2.
Try to move around both tables & see what happen.
 
Last edited:
Upvote 0
Thank you

And i can just use offset to take a different range. I also understand this code better.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top