How do i populate combobox with only part of table

[Rulle]

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:

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

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]

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]

Now you could write your previous script like this:

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

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]

Now you could write your previous script like this:

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

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]

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]

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.