Listbox Header

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
701
Have a named range called "AccidentsHeader"

Within my code I have:

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
How exactly id AccidentsHeader defined?

If your data is in, say, A1:D5 with the header row in A1:D1 then to show the headers correctly in the listbox AccidentsHeader should refer to A2:D5.
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
701
The row it populates is dynamic.

The header row is constant and I haven't yet defined AccidentsHeader.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
If you haven't defined AccidentsHeader yet then you should get an error when you try and use it for the row source property of the listbox.
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
701
I am getting it populated below Column A, Column B, Column C etc...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
The headers for a listbox are taken from the row above the range you use as the row source.

For example if your data, including headers, was in A1:D5 then to have the values from A1:D1 appear as headers in the listbox you would set the RowSource to A2:D5.
 

jamescooper

Well-known Member
Joined
Sep 8, 2014
Messages
701
So if the data is dynamic, i.e. it will not always be row 2 to 6, is there anyway around this?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,632
Office Version
2007
Platform
Windows
Only change one row down in your named range. For example, if your named range has "$A$1:$H$1200" then change it to "$A$2:$H$1200"
Try again
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,632
Office Version
2007
Platform
Windows
Then I did not understand.
Can you explain what you need?

Do you want to fill the listbox by referring to the named range "AccidentsHeader"?
If so, try the following:

VBA Code:
Private Sub CommandButton1_Click()
  Dim r As Range, sh As Worksheet, lr As Long
  Set sh = Sheets(Range("AccidentsHeader").Parent.Name)
  Set r = sh.Range("AccidentsHeader")
  lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
  ListBox1.RowSource = sh.Name & "!" & r.Offset(1).Resize(lr, r.Columns.Count).Address
End Sub
 
Last edited:

Forum statistics

Threads
1,085,153
Messages
5,382,010
Members
401,766
Latest member
uyedaj

Some videos you may like

This Week's Hot Topics

Top