Not getting the Correct Range

[rguy84]

I am really confused what is going on. I am setting a range for a list box, and saying use this specific range but it is looking at the active page still.

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastR As Long
    
    Set ws = Sheets(2)
    lastR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    With Me.lbox
        .ColumnCount = 2
        .ColumnHeads = True
        .RowSource = ws.Range("A2:B" & lastR).Address
    End With
End Sub

So if sheets(1) is active, .rowSource acts if I do sheets(1).Range("A2:B" & lastR).Address vs sheets(2).Range("A2:B" & lastR).Address...
Thoughts

 

[JackDanIce]

I think you need to drop .Address, i.e. try:

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastR As Long
    
    Set ws = Sheets(2)
    lastR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    With Me.lbox
        .ColumnCount = 2
        .ColumnHeads = True
        .RowSource = ws.Range("A2:B" & lastR)
    End With
End Sub

 

[rguy84]

removing .Address throws an error 13, type mismatch

 

[RoryA]

Address does not include the sheet name, so you either need:

        .RowSource = "'" & ws.Name & "'!A2:B" & lastR

or (preferably IMO):

.List = ws.Range("A2:B" & lastR).Value

 

[rguy84]

I did the second option, thanks Rory. Any idea why all the examples I foind were like post 1?

 

[rguy84]

Rory it seems that .list removes the headers

 

[RoryA]

Correct. If you need headers, then you must use Rowsource.

 

[rguy84]

I assume .List just throws an array together where as .rowSource actually loost at the sheet?

 

[RoryA]

Yep. Using Rowsource, your listbox is actually bound to the data. If the data changes, events in the listbox will be triggered, which is why I try to avoid it.

 

[rguy84]

Which would be better then? The list box is being used this discussion: http://www.mrexcel.com/forum/showthread.php?t=557998