I have a problem with displaying column headers in a ListBox in my userform. Everything is working fine but could not show the headers.

My problem is described here:

1. Actual data:

2. VBA code:

Private Sub UserForm_Initialize()
Dim ws1 As Worksheet
Dim rng1 As Range
Dim MyArray1
Set ws1 = Sheets("Accounts")
Set rng1 = ws1.Range("A5:E" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row)
With Me.ListBox10 .BorderStyle = 1 .BorderColor = &H80000012 .BackColor = &H80FFFF .Clear .ColumnHeads = True .ColumnCount = rng1.Columns.Count MyArray1 = rng1 .List = MyArray1 .ColumnWidths = "40,70,60,60,50" End With
End Sub

3. Output result:

Can you please guide me how to set Column Header to ListBox? And format it (column headers)?

Column headers are Sheets("Accounts").Range("A5:E5")

3

2 Answers

Well, when you try loading a list Box otherwise then using its RowSource it starts loading from the row before the header, even if you set ColumnHeads = True. To be more strange, even using RowSource, it shows headers only if the respective range starts one row below the header you need. So, your code should become:

Private Sub UserForm_Initialize() Dim ws1 As Worksheet, rng1 As Range Set ws1 = Sheets("Accounts") Set rng1 = ws1.Range("A5:E" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row) With Me.ListBox1 .BorderStyle = 1 .BorderColor = &H80000012 .BackColor = &H80FFFF .ColumnHeads = True .ColumnCount = rng1.Columns.count .ColumnWidths = "40,70,60,60,50" .RowSource = rng1.Parent.name & "!" & rng1.Resize(rng1.rows.count - 1).Offset(1).address End With
End Sub

2

Update your code to the below. Note that it's using RowSource and the range is starting at A6 rather than A5.

Private Sub UserForm_Initialize() Dim ws1 As Worksheet Dim rng1 As Range 'Dim MyArray1 Set ws1 = Sheets("Accounts") Set rng1 = ws1.Range("A6:E" & ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row) With Me.ListBox10 .BorderStyle = 1 .BorderColor = &H80000012 .BackColor = &H80FFFF .Clear .ColumnHeads = True .ColumnCount = rng1.Columns.Count .RowSource = rng1.Address(External:=True) 'MyArray1 = rng1 '.List = MyArray1 .ColumnWidths = "40,70,60,60,50" End With
End Sub 

Column Headers are another confusing element of the ListBox. If you use the RowSource property to add data to the ListBox then the line above the Range will be automatically used as the header. . . If you use the List or AddItem property to fill the ListBox then the column headers are not available. The best solution, albeit a frustrating one, is to use labels above the ListBox. I know it sounds crazy but that unfortunately is the reality. The one advantage is that you can use the Label click event which is useful if you plan to sort the data by a column.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.