Listbox Header

[jamescooper]

Have a named range called "AccidentsHeader"

Within my code I have:

Private Sub CommandButton1_Click()

ListBox1.RowSource = "AccidentsHeader"

....

End Sub

ColumHeaders set to TRUE

But I keep getting Column A, Column B, Column C... etc...

Any ideas?

 

[Norie]

I don't seem to see any code to populate the listbox in the sub CommandButton1_Click.

 

[jamescooper]

I am unsure which line of code to include for this now.

' sh.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, UBound(ary) + 1).Value = ary
' lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row

Those lines of code populate the record again at the base of the data which is not what it is supposed to do.

 

[Norie]

You would use the same code you used previously to populate the listbox.

  ListBox1.RowSource  = r.Offset(1).Resize(lr, r.Columns.Count).Address(External:=True)

 

[jamescooper]

That doesn't quite work, it throws up an error on that row.

I think it is because it is trying to find the value in row 2, which is now blank?

 

[jamescooper]

Any ideas?

 

[Akuini]

1. Using Rowsource is unreliable especially if you plan to add or delete the list item in the lsitbox.

This is from VBA for smarties How to fill a Combobox / Listbox
It says:
"If you use 'rowsource' you make a direct link to a certain range in a worksheet.
Combined with the combobox it can cause Excel to blackout, because any change in the combobox will be transferred directly to the 'source'.
Excel has proven not to be able to do this correctly.
Besides: the changing of the source at every change in the combobox slows down your code.
You should reduce the reading/writing from/to a workbook in your code as much as possible."

Without Rowsource, you can insert the range directly into the listbox like this:
ListBox1.List = Sheets("Sheet1").Range("A1:D10").Value

2. As for the listbox header, you can create another listbox, place it above the first listbox, populate the table header into it. You can play with many properties to change its appearance like background color, font size etc. Like this: