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")
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 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 https://excelmacromastery.com/vba-listbox/
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.
ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm5taWaCeYWOoaawZaSkerSx02aaqKSlortutMSam56qXZ67bsLBmmSuq5Wns7C%2BzGajoqukl7y5