How do i populate combobox with only part of table

Rulle

New Member
Joined
Nov 9, 2018
Messages
7
I want to populate combobox with the first 3 collums from table1 and the whole table from table2. But I also want my workbook to be dynamic, so I can move the tables around and not affect the userforms comboboxes.

So far I have populatet the comboboxes with this code:
Code:
With combobox1
    .AddItem Sheets("sheet1").Range("b74").Value
    .AddItem Sheets("sheet1").Range("b75").Value
    .AddItem Sheets("sheet1").Range("b76").Value
    .AddItem Sheets("sheet1").Range("b77").Value
End With
With tables I have tried populate the comboboxes in a lot of ways.

Like this


Code:
com_2_1.RowSource = Range("table1").Cells(3#).Address
In this way I can only add a single cell and it erases previous populatet data.

How can I populate data to combobox and not delete previous combo-data and at the same time make my references dynamic.?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,284
Not sure I'm clear here:

You want combobox loaded with values from two different tables??

You earlier said:

I want to populate combobox with the first 3 collums from table1 and the whole table from table2

Is this A Multicolumn Combobox?

Or is the second Table only One column Wide?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,284
Now you could write your previous script like this:

Assuming you want to start on row 74 and go to row 100

Code:
Private Sub CommandButton1_Click()
'Modified  11/9/2018  2:48:43 AM  EST
ComboBox1.Clear
With ComboBox1
For i = 74 To 100
    .AddItem Sheets("sheet1").Cells(i, 2).Value
Next
End With
End Sub
 

Rulle

New Member
Joined
Nov 9, 2018
Messages
7
Now you could write your previous script like this:

Assuming you want to start on row 74 and go to row 100

Code:
Private Sub CommandButton1_Click()
'Modified  11/9/2018  2:48:43 AM  EST
ComboBox1.Clear
With ComboBox1
For i = 74 To 100
    .AddItem Sheets("sheet1").Cells(i, 2).Value
Next
End With
End Sub
Sorry for the misunderstanding.

In the above code it refers to sheet1, but if i move the table to a different sheet, the code stays the same and dosent work. I have over 100 one column tables with different uniqe names. So it is important that the code referes to a specific table, and some times specific collumns in that table.

Hope it made sence
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,284
Your original code does not refer to a Table.

It just refers to a Range on a sheet.

Using a Table you would have to refer to it like this

Sheets(2).Listobjects("Table1")

Are you sure your talking about a excel Table or a excel Sheet.

A Table is a range on a sheet.
 

Rulle

New Member
Joined
Nov 9, 2018
Messages
7
Your original code does not refer to a Table.

It just refers to a Range on a sheet.

Using a Table you would have to refer to it like this

Sheets(2).Listobjects("Table1")

Are you sure your talking about a excel Table or a excel Sheet.

A Table is a range on a sheet.
I can see why you are confused.

So far i have made my references to specific cells and ranges. That is what i showed in the first code example.

But in that way my workbook got messy, so i took a deccision to make my workbook dynamic.

Now i have made all data ranges to 1 collumn named tables. And then i want to refference my comboboxes and listboxes to these tables, but cant figure out how.
 

Forum statistics

Threads
1,082,259
Messages
5,364,100
Members
400,779
Latest member
lumers

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