Populating my combo box

[rharri1972]

Hello! First off, thank you all for lending your knowledge to help those of us that lack in that knowledge. I am so thankful to be able to ask how to complete a needed task and through your teaching or showing I am able to learn by this. Sometimes it just doesn't sink in like it maybe would have when i was younger.

My need at this moment is to populate ComboBox 1 on userform 3. I have a worksheet "Customers Select" and the range is A2:G464 at the moment but will be adding additional customers or rows to this worksheet as I gain more customers.

My hope is to use the combobox on a userform that will be an order form. On the drop down I would like to see my list of customers so I can select the correct one for the current order being placed.

Again, i would like for this to be dynamic so as my worksheet rows grow, the combobox will update accordingly.

Here is the code i currently have that just doesn't work. As matter of fact it returns Can't set rowsource property. Invalid property value.

Me.ComboBox1.RowSource = "Customers Select!A2:G" & Range("G" & Rows.Count).End(xlUp).Row

I have this code set in the userform and the event is on initialize.

I have no code set within the combobox.

again, any help is greatly appreciated.

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
'Modified  7/17/2022  11:10:55 AM  EDT
Dim r As Range
ComboBox1.Clear
Dim Lastrow As Long
    With Sheets("Customers")
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

            For Each r In .Range("A2:G" & Lastrow)
                ComboBox1.AddItem r.Value
            Next
    End With
End Sub

 

[rharri1972]

Try this:

Private Sub CommandButton1_Click()
'Modified  7/17/2022  11:10:55 AM  EDT
Dim r As Range
ComboBox1.Clear
Dim Lastrow As Long
    With Sheets("Customers")
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

            For Each r In .Range("A2:G" & Lastrow)
                ComboBox1.AddItem r.Value
            Next
    End With
End Sub

My Answer is this...can I do this on Userform Initialize? I need this information before hitting any button.

 

[My Aswer Is This]

Try this:

Private Sub UserForm_Initialize()
'Modified  7/17/2022  11:55:00 AM  EDT
Dim r As Range
ComboBox1.Clear
Dim Lastrow As Long
    With Sheets("Customers")
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

            For Each r In .Range("A2:G" & Lastrow)
                ComboBox1.AddItem r.Value
            Next
    End With

End Sub