userform filling combobox with cell values

[austin350s10]

I am working with a userform and trying to load a combobox with values upon initialization. Below is the code i am trying to use which is just supposed to reference a sheet containing the values I want to use. The below code keeps giving me the error: "Run-time error '1004' Application-defined or object-defined error" Any ideas what I'm doing wrong here?

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Sheets("StaffList")


With Sheets("StaffList")
    lastrow = ws.Cells(Rows.Count, 2).End(xlUp).Row
End With



i = 0
With Me.boxNames
    .Clear
    Do
                    .AddItem
                    .List(i, 0) = ws.Cells(i, 1).Value
                    .List(i, 1) = ws.Cells(i, 2).Value
                    i = i + 1
    Loop Until i = lastrow
End With
     
End Sub

 

[ranman256]

Its the '.AddItem'
you actually have to add an item... boxNames.additem "myItem"
so
.addItem ws.Cells(i, 1).Value

 

[mikerickson]

Has the .ColumnCount property of the list box been set to 2?

 

[austin350s10]

I still get the same error. I have even tried taking the comobox out of the equation and that does not work either. even something like this does not work:

myID = ws.Cells(i, 1).Value

 

[austin350s10]

Yes. pretty sure I am just referencing the cell wrong. Can't quite remember how to correctly reference the cell value

 

[MickG]

Try this:-

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim i
Dim lastrow
Set ws = Sheets("StaffList")


With Sheets("StaffList")
    lastrow = ws.Cells(Rows.Count, 2).End(xlUp).Row
End With
i = 1
With Me.Boxnames
    .ColumnCount = 2
    .Clear
    Do
        .AddItem ws.Cells(i, 1).Value
        .List(.ListCount - 1, 1) = ws.Cells(i, 2).Value
        i = i + 1
    Loop Until i = lastrow
.ListIndex = 0
End With
End Sub

Regards Mick

 

[austin350s10]

Mick Thanks a ton! works perfect!!!

 

[MickG]

You're welcome