Userform Combobox RowSource question

[ipbr21054]

Morning,

I am using a userform for data entry to a worksheet.
Currently say Combobox5 is for a certain car part.
In the properties of Combobox5 under RowSource i have INFO!T2:T68 of which is where these specific parts are listed.

My question is When i add more car parts to the list, so now my list is INFO!T2:T100 how does the RowSource update itself ?

As you can see i will not be able to select from the Combobox parts that are in cells T69 & onwards.

Thanks

 

[My Aswer Is This]

You should not use RowSource
You need to put this script in your userform

Private Sub UserForm_Initialize()
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "T").End(xlUp).Row
ComboBox5.List = Sheets("INFO").Cells(2, "T").Resize(lastrowt).Value
End Sub

 

[ipbr21054]

Thanks.

How would i need to code this as there are 11 Comboboxes.

All on the same worksheet INFO but other columns.
Like,
INFO R2
INFO L2:L33
INFO B2:B54

etc etc

If you can advise a couple so i can see then later i will continue to do all of them.

Thanks

 

[My Aswer Is This]

If you look at my script you will see T that means column T

And when you see 2 that means start on row 2

So you will need to write this same code 11 times and modify T for what ever column that Combobox should get its value from.

 

[My Aswer Is This]

I know that sounds like a lot of work.

If all your comboboxes were name:
Combobox1 to 11
The default names

And combobox1 referred to column 1 and Combobox2 referred to column 2

And so on we can make a loop which would do this with only about 4 lines of code.

Or if Combobox 1 referred to column 8 and next combobox referred to column 9

The same would work.

But if there is no consistent logic making a loop will not work.

Is there any consistent logic?

 

[jkpieterse]

I would:
- Make sure row 1 contains the name of each combobox above its list
- Convert each column of sourcedata to a table (converting to a table ensures the names below auto-expand if you add items, make sure there is an empty column between each of them to avoid confusion)
- Assign range names to the tables by selecting each table in turn and choosing Formulas, "Create from Selection" (check "Top row")
- Have code like this to populate the comboboxes:

Sub PopulateBoxes()
    Dim oCtl As Control
    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "ComboBox" Then
            oCtl.List = ThisWorkbook.Names(oCtl.Name).RefersToRange.Value
        End If
    Next
End Sub

 

[My Aswer Is This]

Now see here is a script that would do what you want if your Combobox names were the default names

1 to 11

And combobox1 referred to column 1 and Combobox2 referred to column 2
And on and on. Or some sort of logic.

You could this put this script into your Userform Initilize code like I gave you earlier.

Private Sub CommandButton2_Click()
'Modified  10/3/2018  8:15:32 AM  EDT
Dim Lastrow As Long
Dim i As Long
    For i = 1 To 11
        Lastrow = Sheets("INFO").Cells(Rows.Count, i).End(xlUp).Row - 1
            Me.Controls("Combobox" & i).List = Sheets("INFO").Cells(2, i).Resize(Lastrow).Value
    Next
End Sub

 

[ipbr21054]

If you look at my script you will see T that means column T

And when you see 2 that means start on row 2

So you will need to write this same code 11 times and modify T for what ever column that Combobox should get its value from.

Hi,
The Columns do not follow suit so i have copied the code 12 "not 11 as i thought"
So like below all the way to 12 but i get a compile error, Ambiguous name detected, UserForm_Initialize

Private Sub UserForm_Initialize()Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowt).Value
End Sub
Private Sub UserForm_Initialize()
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "L").End(xlUp).Row
ComboBox2.List = Sheets("INFO").Cells(2, "L").Resize(lastrowt).Value
End Sub
Private Sub UserForm_Initialize()
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "B").End(xlUp).Row
ComboBox3.List = Sheets("INFO").Cells(2, "B").Resize(lastrowt).Value
End Sub
Private Sub UserForm_Initialize()
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "J").End(xlUp).Row
ComboBox4.List = Sheets("INFO").Cells(2, "J").Resize(lastrowt).Value
End Sub

 

[My Aswer Is This]

You need to have it look like this:

I did two you can do the rest.

Lastrowl means last row column L

Lastrowr means last row column R

So you need to always change both the R and the r

For each situation:

Here is how to do two.

​

Private Sub UserForm_Initialize()
'For colum T
Dim lastrowt As Long
lastrowt = Sheets("INFO").Cells(Rows.Count, "T").End(xlUp).Row
ComboBox1.List = Sheets("INFO").Cells(2, "T").Resize(lastrowt).Value
'For column L
Dim lastrowl As Long
lastrowl = Sheets("INFO").Cells(Rows.Count, "L").End(xlUp).Row
ComboBox2.List = Sheets("INFO").Cells(2, "L").Resize(lastrowl).Value

End Sub

 

[jkpieterse]

I would suggest trying my solution as it is more robust.