Populate combobox with different dynamic tables

[Rulle]

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.

 

[Akuini]

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:

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.

 

[Rulle]

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?

 

[dotchiejack]

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

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

 

[Rulle]

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

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.

 

[Akuini]

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:

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.

 

[Rulle]

Thank you

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

 

[Akuini]

Thank you

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

You're welcome & thanks for replying