Subscript out of range Problem??? Please Help!! 1

[ribhead]

I am trying to write some code that will put information in to a combobox (Column A). I keep gettin a Subscript out of range error and I can't see the problem. I'm too new to know when I mess something up. Can anyone please tell me where my error is?
I would appreciate any help given.

Private Sub UserForm_Initialize()
Dim listitems As Variant
Dim mysource As Workbook
Dim i As Integer
Dim lastrow As Double
lastrow = Rows.Cells(65536, 1).End(xlUp).Row
Set mysource = ActiveWorkbook
listitems = mysource.Worksheets(1).Range(Cells(1, 1), Cells(lastrow, 1)).Value
With Me.ComboBox1
.Clear
For i = 1 To UBound(listitems)
.AddItem listitems(i)
Next i
End With
End Sub

For just $19.95 you too can have a beautiful smile.

 

[Bowers74]

Hmmm, I've never seen it done that way. ;-)

Try this:

Private Sub UserForm_Initialize()
Dim i As Integer, listitems as range, lastrow As Long
lastrow = Cells(65536, 1).End(xlUp).Row
listitems = thisworkbook.worksheets(1).Range("A1:A" & lastrow)
With Me.ComboBox1
    .Clear
    .Rowsouce = listitems.address
End With
End Sub

That should do it!

I left the worksheet and range references as you had them, but may I suggest that you take a look at my FAQ faq707-4090 - Refer to Worksheets more effecively in a Procedure


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ribhead]

Well it still doesn't work. Now I am getting an Object variable or With variable not set? Any other thoughts???

For just $19.95 you too can have a beautiful smile.

 

[Bowers74]

Ooops,

Private Sub UserForm_Initialize()
Dim i As Integer, listitems as range, lastrow As Long
lastrow = Cells(65536, 1).End(xlUp).Row

set

 listitems = thisworkbook.worksheets(1).Range("A1:A" & lastrow)
With Me.ComboBox1
    .Clear
    .Rowsouce = listitems.address
End With
End Sub

Sorry, it's 1:00 a.m. here in Germany, and I need to get some sleep so I stop making stupid mistakes.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ribhead]

Cool beans Mr. Bowers you get a star from me. Thanks for showing me an easier way as well.

Mr. Ribhead

For just $19.95 you too can have a beautiful smile.