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

 

[ipbr21054]

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

Hi,
The items are set up as you advised already so i just need to apply the code.

As i have 12 ComboBoxes do i write it 12 times ?

Also which part of your code do i need to change for it to apply to the list.
can you highlight it please.

 

[ipbr21054]

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

@MAIT
As you can see i have now changed it as advised,and hope its correct ?
I still get the access denied message and text in yellow as shown.

I need to try the other option mentioned as i need to get this working tonight.

Unless you can see a error on my behalf that is

 

[Norie]

Have you cleared the RowSource property of the comboboxes you are trying to populate?

 

[jkpieterse]

Place my code in your userform's module and make sure the UserForm_Initialize routine looks like this:

Private Sub UserForm_Initialize()
    PopulateBoxes
End Sub

So the entire code in the form to fill the boxes looks like this:

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

Private Sub UserForm_Initialize()
    PopulateBoxes
End Sub

 

[ipbr21054]

Have you cleared the RowSource property of the comboboxes you are trying to populate?

Yes.
I have now gone through all the code again & now getting somewhere even though i typed it like for like.

I then checked all the comboboxes and only have an issue with 2 of them.
There code is shown below.

I click on the down arrow & i see only 2 options to select from when there are say 20 in the INFO sheet.

Do you see anything wrong with this code ??

'For column BDim lastrowb As Long
lastrowb = Sheets("INFO").Cells(Rows.Count, "B").End(xlUp).Row
ComboBox3.List = Sheets("INFO").Cells(2, "B").Resize(lastrowr).Value


'For column J
Dim lastrowj As Long
lastrowj = Sheets("INFO").Cells(Rows.Count, "J").End(xlUp).Row
ComboBox4.List = Sheets("INFO").Cells(2, "J").Resize(lastrowr).Value

 

[My Aswer Is This]

Yes I can see several errors in your code:

Private Sub UserForm_Initialize()
'For column B
Dim lastrowb As Long
lastrowb = Sheets("INFO").Cells(Rows.Count, "B").End(xlUp).Row
ComboBox3.List = Sheets("INFO").Cells(2, "B").Resize(lastrowb).Value

'For column J
Dim lastrow[COLOR=#ff0000]j[/COLOR] As Long
lastrow[COLOR=#ff0000]j[/COLOR] = Sheets("INFO").Cells(Rows.Count, "[COLOR=#ff0000]J[/COLOR]").End(xlUp).Row
ComboBox4.List = Sheets("INFO").Cells(2, "[COLOR=#ff0000]J[/COLOR]").Resize(lastrow[COLOR=#ff0000]j[/COLOR]).Value

End Sub

If it's for J you have to have J in 5 places like this:

See code in red

 

[ipbr21054]

Hi,
Yes its now sorted and works great.

many thanks for all the advice.

Once last thing to finish this off.
When i open the userform the date needs to automatically populate TextBox2

Here the whole code if you you could advise the code & where i need to put it thanks.

Private Sub CommandButton1_Click()

    With Sheets("DATABASE")
     Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("A6").Select
     Range("A6:Q6").Borders.LineStyle = xlContinuous
     Range("A6:Q6").Borders.Weight = xlThin
     Range("A6:Q6").Interior.ColorIndex = 6
     Range("M6") = Date
     Range("$Q$6").Value = "'NO NOTES FOR THIS CUSTOMER"
     Range("$Q$6").HorizontalAlignment = xlCenter
     
    .Range("A6").Value = Me.TextBox1.Text
    .Range("B6").Value = Me.ComboBox1.Text
    .Range("C6").Value = Me.ComboBox2.Text
    .Range("D6").Value = Me.ComboBox3.Text
    .Range("E6").Value = Me.ComboBox4.Text
    .Range("F6").Value = Me.ComboBox5.Text
    .Range("G6").Value = Me.ComboBox6.Text
    .Range("H6").Value = Me.ComboBox7.Text
    .Range("I6").Value = Me.ComboBox8.Text
    .Range("J6").Value = Me.ComboBox9.Text
    .Range("K6").Value = Me.ComboBox10.Text
    .Range("L6").Value = Me.ComboBox11.Text
    .Range("M6").Value = Me.TextBox2.Text
    .Range("N6").Value = Me.ComboBox12.Text
    .Range("O6").Value = Me.TextBox3.Text
    .Range("P6").Value = Me.TextBox4.Text
    .Range("Q6").Value = Me.TextBox5.Text
End With


Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
                Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.combobox
            ctrl.Value = ""
    End Select
    
Next ctrl
    
    TextBox2.Value = Now
    TextBox2 = Format(TextBox2.Value, "dd/mm/yyyy")
    TextBox1.SetFocus


End Sub
Private Sub CommandButton2_Click()
Unload DatabaseInput
End Sub
Private Sub TextBox1_Change()
    TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
    TextBox2 = UCase(TextBox2)
End Sub
Private Sub TextBox3_Change()
    TextBox3 = UCase(TextBox3)
End Sub
Private Sub TextBox4_Change()
    TextBox4 = UCase(TextBox4)
End Sub


Private Sub UserForm_Initialize()
'REGISTRATION NUMBER
Dim lastrowr As Long
lastrowr = Sheets("INFO").Cells(Rows.Count, "R").End(xlUp).Row
ComboBox1.List = Sheets("INFO").Cells(2, "R").Resize(lastrowr).Value


'BLANK USED
Dim lastrowl As Long
lastrowl = Sheets("INFO").Cells(Rows.Count, "L").End(xlUp).Row
ComboBox2.List = Sheets("INFO").Cells(2, "L").Resize(lastrowl).Value


'VEHICLE
Dim lastrowb As Long
lastrowb = Sheets("INFO").Cells(Rows.Count, "B").End(xlUp).Row
ComboBox3.List = Sheets("INFO").Cells(2, "B").Resize(lastrowb).Value


'BUTTONS
Dim lastrowj As Long
lastrowj = Sheets("INFO").Cells(Rows.Count, "J").End(xlUp).Row
ComboBox4.List = Sheets("INFO").Cells(2, "J").Resize(lastrowj).Value


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


'TRANSPONDER CHIP
Dim lastrowf As Long
lastrowf = Sheets("INFO").Cells(Rows.Count, "F").End(xlUp).Row
ComboBox6.List = Sheets("INFO").Cells(2, "F").Resize(lastrowf).Value


'JOB ACTION
Dim lastrowh As Long
lastrowh = Sheets("INFO").Cells(Rows.Count, "H").End(xlUp).Row
ComboBox7.List = Sheets("INFO").Cells(2, "H").Resize(lastrowh).Value


'PROGRAMMER USED
Dim lastrowd As Long
lastrowd = Sheets("INFO").Cells(Rows.Count, "D").End(xlUp).Row
ComboBox8.List = Sheets("INFO").Cells(2, "D").Resize(lastrowd).Value


'KEY CODE
Dim lastrowp As Long
lastrowp = Sheets("INFO").Cells(Rows.Count, "P").End(xlUp).Row
ComboBox9.List = Sheets("INFO").Cells(2, "P").Resize(lastrowp).Value


'BITING
Dim lastrowx As Long
lastrowx = Sheets("INFO").Cells(Rows.Count, "X").End(xlUp).Row
ComboBox10.List = Sheets("INFO").Cells(2, "X").Resize(lastrowx).Value


'CHASSIS NUMBER
Dim lastrown As Long
lastrown = Sheets("INFO").Cells(Rows.Count, "N").End(xlUp).Row
ComboBox11.List = Sheets("INFO").Cells(2, "N").Resize(lastrown).Value


'VEHCILE YEAR
Dim lastrowv As Long
lastrowv = Sheets("INFO").Cells(Rows.Count, "V").End(xlUp).Row
ComboBox12.List = Sheets("INFO").Cells(2, "V").Resize(lastrowv).Value


End Sub

 

[My Aswer Is This]

You said:

When i open the userform the date needs to automatically populate TextBox2

Any time you want something to happen automatically you need to put it in the:

Private Sub UserForm_Initialize()


Code.

So just add this line of Code to your other code in the Initialize code
Maybe after the code you have in it now.

You would need to add a line of code like this

Textbox1.Value=Date

Assuming you want Todays date put in that Textbox1​

_{^{<strike>
</strike>}}​