0

Below is the code I created:

Private Sub UserForm_Activate()

Dim Total_rows_PU As Long
Total_rows_PU = Workbooks("Revised-Payroll (VBA Copy).xlsm").Worksheets("Payroll Update").Range("A" & Rows.Count).End(xlUp).Row

Me.cbxName.List = Worksheets("Payroll Update").Range("A2:A" & Total_rows_PU)

End Sub

This is the error I receive:

Error Dialogue Box

This is where the error occurs: Line of Error

Working solution after considering the answer below:

Private Sub UserForm_Initialize()

Total_rows_PU = Workbooks("Revised-Payroll (VBA Copy).xlsm").Worksheets("Payroll Update").Range("A" & Rows.Count).End(xlUp).Row

Dim cell As Range

For Each cell In Range("A2:A" & Total_rows_PU)
    Me.cbxName.AddItem cell.Value
Next
End Sub
Community
  • 1
  • 1
Pherdindy
  • 1,168
  • 7
  • 23
  • 52
  • 1
    In your line `Me.cbxName.List = Worksheets("Payroll Update").Range("A2:A & Total_rows_PU")` you've misplaced your closing quote. It should be `Me.cbxName.List = Worksheets("Payroll Update").Range("A2:A" & Total_rows_PU)`. – TotsieMae Jan 30 '18 at 06:44
  • Thank you for that. I revised the main post and came up with a new error. – Pherdindy Jan 30 '18 at 06:46

1 Answers1

1

Here's the right way of populating combobox:

Private Sub UserForm_Initialize()
Dim cell As Range

For Each cell In Range("A1:A9")
    Me.ComboBox1.AddItem cell.Value
Next

End Sub

It populates combobox with values from cells in range A1:A9 (you have to specifiy your own). Here's how it works:

enter image description here

Values indicate cell address :)

Of course, @TotsieMae pointed lot's of good resources - I recommend them :)

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • Thank you this works well even dynamically. :). They are good references, but more complicated than it should be and does not really have what I need right now. Updated the main post for the fixed code. – Pherdindy Jan 30 '18 at 07:18