ListBox Does Not Populate

[wsnyder]

Hi all,

Using Excel 365.

I'm trying to populate a ListBox on a UserForm from a ListObject.
But when I run it, the ListBox is empty.

Do you see anything wrong in the code?
There is 1 worksheet with 1 Table.

Private Sub frmRegion_Initialize()

'Populate list values

    'Objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim lo As ListObject
        Dim arr As Variant
        Dim myList() As String
    
    'Variables
        Dim i As Long
    
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets(1)
        Set lo = ws.ListObjects(1)
        arr = lo.DataBodyRange
        ReDim myList(1 To UBound(arr))
    
    'Populate ComboBox from array
        For i = LBound(myList) To UBound(myList)
           myList(i) = arr(i)
        Next i
        
    'Assign array to combo box
        lstRegion.List = myList

End Sub

Thanks,
-w

 

[dmt32]

try changing this

Private Sub frmRegion_Initialize

Back to this

Private Sub UserForm_Initialize()

and see if this resolves your problem.

Regardless of userforms name, you should never rename the event code - it is Always UserForm_Initialize

Dave

 

[Fluff]

Added to what dmt32 has said, arr is a 2d array, not a 1d array.
Are you trying to put the entire table into the listbox, or just the 1st column?

 

[wsnyder]

1st Column
The Table has a single column

I changed UserForm_Initialize.
The listbox looks a bit different with a horizontal scrollbar at bottom, but still no values are visible to select
The Table has 4 values

Thanks,
-w

 

[Fluff]

In that case you can just load the listbox directly like

        lstRegion.List = lo.DataBodyRange.Value

 

[JEC]

*snip*

 

[wsnyder]

Thanks all,

No array needed, changing the name of the initialization event and using the value of the ListObject.DataBodyRange did the trick.

Thanks,
-w

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[wsnyder]

I connected a listbox on a new userform but it is throwing an error during initialization

Run-time error '-2147352571 (80020005)':
Could not set the Value property. Type mismatch

The Table has 1,370 rows.

Private Sub UserForm_Initialize()

    'Objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim lo As ListObject
        
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("dataSuppliers")
        Set lo = ws.ListObjects(1)
        
    'Populate listbox
        lstBoxSuppliers = lo.DataBodyRange.Value
        
    'Tidy up
        Set lo = Nothing
        Set ws = Nothing
        Set wb = Nothing
        
End Sub

The worksheet name is correct.
The worksheet only has 1 Table.
Seems I have not reached max number of items?
Is there a better way to present the data to the user and let them select 1 or more items?

Thanks,
-w

 

[wsnyder]

Forgot list here:
lstSuppliers.list = lo.DataBodyRange.Value

Works now, but is there a better way?

Thanks,
-w