Trying to get row source in VBA Listbox

[Eric Penfold]

I have code to get a range into form listbox but it says run time error 13??

Private Sub UserForm_Initialize()

    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim LRow   As Long

    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    Me.SuppliersName.RowSource = ws.Range("B1:B" & LRow)
    Me.SuppliersName.ColumnHeads = True
    
End Sub

 

[Skyybot]

Try this:

Private Sub UserForm_Initialize()

    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim LRow   As Long

    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
    LRow = ws.UsedRange.Rows.Count
    
    Me.SuppliersName.ListFillRange = ws.Range("B1:B" & LRow).Address
    Me.SuppliersName.ColumnHeads = True
    
End Sub

 

[RoryA]

You'll want to include the sheet name in the rowsource as well as the cell addresses:

Me.SuppliersName.RowSource = "'" & ws.name & "'!B1:B" & LRow