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

Question

Is it possible to set up the headers in a multicolumn listbox without using a worksheet range as the source   The following uses an array of variants which is assigned to the list property of the listbox  the headers appear blank   Sub testMultiColumnLb       ReDim arr 1 To 3  1 To 2       arr 1  1     1      arr 1  2     One      arr 2  1     2      arr 2  2     Two      arr 3  1     3      arr 3  2     Three        With ufTestUserForm lbTest          Clear          ColumnCount   2          List   arr     End With      ufTestUserForm Show 1 End Sub

User · Answer

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 :)

User · Answer

Here s one approach which automates creating labels above each column of a listbox  on a worksheet    It will work  though not super-pretty   as long as there s no horizontal scrollbar on your listbox   Sub Tester   Dim i As Long  With Me lbTest      Clear      ColumnCount   5      must do this next step       ColumnWidths    70 60 100 60 60       ListStyle   fmListStylePlain     Debug Print  ColumnWidths     For i   0 To 10          AddItem          List i  0     blah   amp  i          List i  1     blah           List i  2     blah           List i  3     blah           List i  4     blah      Next i  End With  LabelHeaders Me lbTest  Array  Header1    Header2                           Header3    Header4    Header5    End Sub  Sub LabelHeaders lb  arrHeaders       Const LBL HT As Long   15     Dim T  L  shp As Shape  cw As String  arr     Dim i As Long  w       delete any previous headers for this listbox     For i   lb Parent Shapes Count To 1 Step -1         If lb Parent Shapes i  Name Like lb Name  amp       Then             lb Parent Shapes i  Delete         End If     Next i       get an array of column widths     cw   lb ColumnWidths     If Len cw    0 Then Exit Sub     cw   Replace cw    pt           arr   Split cw             start points for labels     T   lb Top - LBL HT     L   lb Left      For i   LBound arr  To UBound arr          w   CLng arr i           If i   UBound arr  And  L   w   lt  lb Width Then w   lb Width - L         Set shp   ActiveSheet Shapes AddShape msoShapeRectangle                                             L  T  w  LBL HT          With shp              Name   lb Name  amp       amp  i              do some formatting              Line ForeColor RGB   vbBlack              Line Weight   1              Fill ForeColor RGB   RGB 220  220  220               TextFrame2 TextRange Characters Text   arrHeaders i               TextFrame2 TextRange Font Size   9              TextFrame2 TextRange Font Fill ForeColor RGB   vbBlack         End With         L   L   w     Next i End Sub

User · Answer

I was searching for quite a while for a solution to add a header without using a separate sheet and copy everything into the userform    My solution is to use the first row as header and run it through an if condition and add additional items underneath    Like that     x000D   x000D  If lborowcount   0 Then x000D   With lboorder x000D    ColumnCount   5 x000D    AddItem x000D    Column 0  lborowcount     Item  x000D    Column 1  lborowcount     Description  x000D    Column 2  lborowcount     Ordered  x000D    Column 3  lborowcount     Rate  x000D    Column 4  lborowcount     Amount  x000D   End With x000D   lborowcount   lborowcount   1 x000D  End If x000D           x000D           x000D  With lboorder x000D    ColumnCount   5 x000D    AddItem x000D    Column 0  lborowcount    itemselected x000D    Column 1  lborowcount    descriptionselected x000D    Column 2  lborowcount    orderedselected x000D    Column 3  lborowcount    rateselected x000D    Column 4  lborowcount    amountselected x000D    x000D    x000D   End With x000D   x000D  lborowcount   lborowcount   1 x000D   x000D   x000D    in that example lboorder is the listbox  lborowcount counts at which row to add the next listbox item  It s a 5 column listbox  Not ideal but it works and when you have to scroll horizontally the  header  stays above the row

User · Answer

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     Then you call the function CreateListBoxHeader  to make the alignment correct and add header items   Result     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

User · Answer

You can give this a try  I am quite new to the forum but wanted to offer something that worked for me since I ve gotten so much help from this site in the past  This is essentially a variation of the above  but I found it simpler   Just paste this into the Userform Initialize section of your userform code  Note you must already have a listbox on the userform or have it created dynamically above this code  Also please note the Array is a list of headings  below as  Header1    Header2  etc  Replace these with your own headings  This code will then set up a heading bar at the top based on the column widths of the list box  Sorry it doesn t scroll - it s fixed labels    More senior coders - please feel free to comment or improve this       Dim Mywidths As String     Dim Arrwidths  Arrheaders As Variant     Dim ColCounter  Labelleft As Long     Dim theLabel As Object                       Other code here that you would already have in the Userform Initialize section       Set theLabel   Me Controls Add  Forms Label 1    Test   amp  ColCounter  True              With theLabel                      Left   ListBox1 Left                      Top   ListBox1 Top - 10                      Width   ListBox1 Width - 1                      Height   10                      BackColor   RGB 200  200  200              End With             Arrheaders   Array  Header1    Header2    Header3    Header4                Mywidths   Me ListBox1 ColumnWidths             Mywidths   Replace Mywidths    pt                   Arrwidths   Split Mywidths                   Labelleft   ListBox1 Left   18             For ColCounter   LBound Arrwidths  To UBound Arrwidths                          If Arrwidths ColCounter   gt  0 Then                                 Header   Header   1                                 Set theLabel   Me Controls Add  Forms Label 1    Test   amp  ColCounter  True                                   With theLabel                                      Caption   Arrheaders Header - 1                                       Left   Labelleft                                      Width   Arrwidths ColCounter                                       Height   10                                      Top   ListBox1 Top - 10                                      BackColor   RGB 200  200  200                                       Font Bold   True                                 End With                                  Labelleft   Labelleft   Arrwidths ColCounter                           End If              Next

User · Answer

I like to use the following approach for headers on a ComboBox where the CboBx is not loaded from a worksheet  data from sql for example    The reason I specify not from a worksheet is that I think the only way to get RowSource to work is if you load from a worksheet   This works for me     Create your ComboBox and create a ListBox with an identical layout but just one row  Place the ListBox directly on top of the ComboBox  In your VBA  load ListBox row1 with the desired headers  In your VBA for the action yourListBoxName Click  enter the following code   yourComboBoxName Activate  yourComboBoxName DropDown   When you click on the listbox  the combobox will drop down and function normally while the headings  in the listbox  remain above the list

User · Answer

Here s my solution   I noticed that when I specify the listbox s rowsource via the properties window in the VBE  the headers pop up no problem  Its only when we try define the rowsource through VBA code that the headers get lost   So I first went a defined the listboxes rowsource as a named range in the VBE for via the properties window  then I can reset the rowsource in VBA code after that  The headers still show up every time   I am using this in combination with an advanced filter macro from a listobject  which then creates another  filtered  listobject on which the rowsource is based   This worked for me

User · Answer

Why not just add Labels to the top of the Listbox and if changes are needed  the only thing you need to programmatically change are the labels

User · Answer

There is very easy solution to show headers at the top of multi columns list box  Just change the property value to  true  for  columnheads  which is false by default   After that Just mention the data range in property  rowsource  excluding header from the data range and header should be at first top row of data range then it will pick the header automatically and you header will be freezed   if suppose you have data in range  A1 H100  and header at  A1 H1  which is the first row then your data range should be  A2 H100  which needs to mention in property  rowsource  and  columnheads  perperty value should be true  Regards  Asif Hameed

User · Answer

Simple answer  no   What I ve done in the past is load the headings into row 0 then set the ListIndex to 0 when displaying the form   This then highlights the  headings  in blue  giving the appearance of a header   The form action buttons are ignored if the ListIndex remains at zero  so these values can never be selected   Of course  as soon as another list item is selected  the heading loses focus  but by this time their job is done   Doing things this way also allows you to have headings that scroll horizontally  which is difficult impossible to do with separate labels that float above the listbox   The flipside is that the headings do not remain visible if the listbox needs to scroll vertically   Basically  it s a compromise that works in the situations I ve been in

User · Answer

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

User · Answer

Just use two Listboxes  one for header and other for data   for headers - set RowSource property to top row e g  Incidents Q4 S4 for data  - set Row Source Property to Incidents Q5 S10   SpecialEffects to  3-frmSpecialEffectsEtched

User · Answer

Another variant on Lunatik s response is to use a local boolean and the change event so that the row can be highlighted upon initializing  but deselected and blocked after a selection change is made by the user   Private Sub lbx Change        If Not bHighlight Then          If Me lbx Selected 0  Then Me lbx Selected 0    False      End If      bHighlight   False  End Sub   When the listbox is initialized you then set bHighlight and lbx Selected 0    True  which will allow the header-row to initialize selected  afterwards  the first change will deselect and prevent the row from being selected again

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

Examples related to vba

Examples related to excel