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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,362
Office Version
2013
Platform
Windows
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.
 

Rulle

New Member
Joined
Nov 9, 2018
Messages
7
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

New Member
Joined
Nov 7, 2017
Messages
2
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
 

Rulle

New Member
Joined
Nov 9, 2018
Messages
7
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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,362
Office Version
2013
Platform
Windows
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:

Rulle

New Member
Joined
Nov 9, 2018
Messages
7
Thank you

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,362
Office Version
2013
Platform
Windows
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
 

Forum statistics

Threads
1,082,151
Messages
5,363,430
Members
400,736
Latest member
Aida

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top