How to add headers to a multicolumn listbox in an Excel userform using VBA

Solution 1:

No. I create labels above the listbox to serve as headers. You might think that it's a royal pain to change labels every time your lisbox changes. You'd be right - it is a pain. It's a pain to set up the first time, much less changes. But I haven't found a better way.

Solution 2:

Here is my approach to solve the problem:

This solution requires you to add a second ListBox element and place it above the first one.

Like this:

Add an additional ListBox

Then you call the function CreateListBoxHeader to make the alignment correct and add header items.

Result:

Call the function CreateListBoxHeader

Code:

  Public Sub CreateListBoxHeader(body As MSForms.ListBox, header As MSForms.ListBox, arrHeaders)
            ' make column count match
            header.ColumnCount = body.ColumnCount
            header.ColumnWidths = body.ColumnWidths

        ' add header elements
        header.Clear
        header.AddItem
        Dim i As Integer
        For i = 0 To UBound(arrHeaders)
            header.List(0, i) = arrHeaders(i)
        Next i

        ' make it pretty
        body.ZOrder (1)
        header.ZOrder (0)
        header.SpecialEffect = fmSpecialEffectFlat
        header.BackColor = RGB(200, 200, 200)
        header.Height = 10

        ' align header to body (should be done last!)
        header.Width = body.Width
        header.Left = body.Left
        header.Top = body.Top - (header.Height - 1)
End Sub

Usage:

Private Sub UserForm_Activate()
    Call CreateListBoxHeader(Me.listBox_Body, Me.listBox_Header, Array("Header 1", "Header 2"))
End Sub

Solution 3:

I was looking at this problem just now and found this solution. If your RowSource points to a range of cells, the column headings in a multi-column listbox are taken from the cells immediately above the RowSource.

Using the example pictured here, inside the listbox, the words Symbol and Name appear as title headings. When I changed the word Name in cell AB1, then opened the form in the VBE again, the column headings changed.

Screenshot displaying a named range and the column headings outside the range.

The example came from a workbook in VBA For Modelers by S. Christian Albright, and I was trying to figure out how he got the column headings in his listbox :)