Bring table with all columns into List Box.

**Medex** · 08-29-2018, 09:00 AM

Hello Dear Members,

My question is about ListBox.

I have a table in which shown information about workers and this table is dynamic.

Actually this table is my database relate to workers' information.

Sometimes I need to make some changes on date, so I need to bring that data into list box.

Here is my code:

I got debug with last part.

 Sub PullDataIntoListBox()

Dim LRow As Long
Dim LCol As Long

EditData.Show

With Worksheets("MainDataBase")
    LRow = Cells(Rows.Count, "A").End(xlUp).Row
    LCol = Cells(4, Columns.Count).End(xlToLeft).Column
    .RowSource = Sheets("MainDataBase").Name & "!" & _
             Sheets("MainDataBase").Range("LRow:LCol").Address
End With

End Sub

Thanks in advance!

**CK76** · 08-29-2018, 09:37 AM

There are multiple issues with your code...
1. While you use "With... End With" you didn't use "." in front of Cells. So it's using ActiveSheet regardless of Worksheet specified.
2. ".RowSource" does not belong in "With... End With" it should be qualified with ListBox.
3. From "EditData.Show" I assume the listbox is in the userform. You should have code to load listbox with data in UserForm initialize event.

Here's sample code using Excel Table to fill listbox.

Private Sub UserForm_Initialize()
Dim ar
With Sheets("MainDataBase")
    ar = .ListObjects("Table1").DataBodyRange.Value
End With
With Me.ListBox1
    .ColumnCount = UBound(ar, 2)
    .List = ar
End With
End Sub

See attached sample as well.