Populating header of Listbox from column header

Question

1. Background & purpose
I'm creating a userform to display data from the Excel sheet("DATA") with table ("Tab1") of multi-columns like below picture.

In my form ("TaskMngUserForm"), after clicking on "Task List" button, all data from Tab1 will be displayed on Listbox1 as follows:

• Column header in Tab1 will be displayed on Listbox1 as Header.
• Data from 2nd row to end in Tab1 will be diplay on Listbox1 corresponding to each columns.

Also I'm adding an event for action "Listbox1_Click()" that returns "Data" sheet row corresponding to the selected Index, from the second column of the selected ListBox1 row.

UserForm and Listbox

2. Code

'4. Event for "Tasks List" button
Private Sub Button_TaskList_Click()

ListBox1.ColumnWidths = "20;100;80;100;60;100;80;80;80;200;200;200"
ListBox1.ColumnCount = 12

With ListBox1

    '.ColumnHeads = True
    .List = Sheets("DATA").Range("B2").CurrentRegion.Value
    .RemoveItem (0)
    .ColumnCount = Sheets("DATA").Cells(2, 2).CurrentRegion.Columns.Count
            
End With

Application.ScreenUpdating = True
Label25.Caption = "Total Tasks: " & (Worksheets("DATA").UsedRange.Rows.Count - 1)

End Sub

'6. Event for "Click Listbox" Action
Private Sub ListBox1_Click()

Dim strAddress As String
Dim dataSht As Worksheet
        
With Me
    If .ListBox1.ListIndex <> -1 Then
        Set dataSht = Sheets("DATA")
        
        If IsNull(Me.ListBox1.Value) Then
            Call MsgBox("You are selecting on blank row item" & vbNewLine & "Be careful!", vbInformation, "Notification")
            Button_TaskList_Click
            
        Else
            strAddress = GetIndexRow(.ListBox1.List(.ListBox1.ListIndex, 0), dataSht.Columns("A"))
                
            '<~~  GetIndexRow returns "Data" sheet row corresponding to the selected Index, which is got from the 2nd column of the selected ListBox row
        
        
            TaskMngUserForm.txtIndex.Value = dataSht.Range("A" & strAddress).Value
            TaskMngUserForm.cmbSource.Value = dataSht.Range("B" & strAddress).Value
            TaskMngUserForm.cmbType.Value = dataSht.Range("C" & strAddress).Value
            TaskMngUserForm.cmbCategory.Value = dataSht.Range("D" & strAddress).Value
            TaskMngUserForm.cmbPriority.Value = dataSht.Range("E" & strAddress).Value
            TaskMngUserForm.cmbTaskOwner.Value = dataSht.Range("F" & strAddress).Value
            TaskMngUserForm.cmbStatus.Value = dataSht.Range("G" & strAddress).Value
            TaskMngUserForm.txtOpenDate.Value = dataSht.Range("H" & strAddress).Value
            TaskMngUserForm.txtCloseDate.Value = dataSht.Range("I" & strAddress).Value
            TaskMngUserForm.txtSubject.Value = dataSht.Range("J" & strAddress).Value
            TaskMngUserForm.txtDescription.Value = dataSht.Range("K" & strAddress).Value
            TaskMngUserForm.txtSolution.Value = dataSht.Range("L" & strAddress).Value
            
        End If
'   TaskMngUserForm.Show
    End If
End With

Application.ScreenUpdating = True
Label25.Caption = "Check in Task.No:  " & txtIndex.Text

End Sub

3. Problem
I can load data from Tab1 to Listbox1 but I cannot populate column header from Tab1 to Header in Listbox1.

Solution

I recently coded a UserForm to include headers and I can answer this for you.

There is only 1 way to populate the headers on a ListBox and that is when you use the ListBox1.RowSource property. In the RowSource property you must assign a Range, this is one example:

UserForm1.ListBox1.RowSource = "Sheet1!A2:H20"

This will populate the data from A2 to H20 on ListBox1 and if the ListBox1 ColumnHeaders property is set to True then anything on Sheet1!A1:H1 will become the headers. This is the only way.

The reason that many users will tell you to just add text labels on top of the ListBox to make it easier is because when you do your list using RowSource, you must always find out what is the last Row used on your Range before you assign the Range to avoid Empty lines on your ListBox. What this means is that if you have 20 rows of data and you assign a range that contains 50 rows, the listbox will populate 50 rows, the last 30 will be empty.

Comments

[Holmes]

Thanks for your prompt comment. Got it. I tried to modify my code like this but column heads are still not displayed. Is there anything wrong here? Set rng = Sheets("DATA").Range("A1").CurrentRegion.Offset(1, 0).Resize(rng.Rows.Count - 1) With ListBox1 .List = rng.Value .ColumnHeads = True .ColumnCount = Sheets("DATA").Cells(2, 2).CurrentRegion.Columns.Count .ColumnWidths = "20;80;90;80;80;50;50;100;80;80;80;80;150;150;150" .ColumnCount = 15 End With

[Rory]

@Holmes as stated in the answer, you have to use the Rowsource property, not the List property.

[Holmes]

@Rory: I tried to use RowSource property but nothing to display in Listbox1. Set rng = Sheets("DATA").Range("A1").CurrentRegion.Offset(1, 0).Resize(rng.Rows.Count - 1) With ListBox1 .RowSource = rng.Address .ColumnHeads = True .ColumnCount = Sheets("DATA").Cells(2, 2).CurrentRegion.Columns.Count .ColumnWidths = "20;80;90;80;80;50;50;100;80;80;80;80;150;150;150" .ColumnCount = 15 End With

[Rory]

@Holmes you need to include the sheet name if it's not the active sheet: .Rowsource = "DATA!" & rng.address

[Holmes]

Thank you all. I solved this problem with this code: ".RowSource = rng.Address(external:=True)". Really appreciated your helps.