Populating Column Headers in a ListBox
 

Hi there,

I am using the following code to populate a listbox with
multiple-column entries. My problem is I can't find a way
to populate the columns' headers too. Any ideas muchly
appreciated.
KL

-------------Code Start--------------
Dim MyArray()

Private Sub UserForm_Initialize()
Dim r As Single
cCounter = Sheets("DBASE").UsedRange.Columns.Count - 1
rCounter = Sheets("DBASE").UsedRange.Rows.Count - 1
ReDim MyArray(rCounter, cCounter)

lb1.ColumnCount = cCounter + 1

'Load values MyArray
For r = 0 To rCounter
For c = 0 To cCounter
MyArray(r, c) = Sheets
("DATABASE").UsedRange.Cells(r + 2, c + 1)
Next c
Next r

'Load ListBox1
lb1.List() = MyArray
End Sub
-------------Code Start--------------

Populating Column Headers in a ListBox
 

Hi

Listbox column headers work only when a listbox' Listfillrange / Rowsource
is set to a range of cells that has a header row. You can't do it populating
by code.

HTH. best wishes Harald

"KL" skrev i melding
...
Hi there,

I am using the following code to populate a listbox with
multiple-column entries. My problem is I can't find a way
to populate the columns' headers too. Any ideas muchly
appreciated.
KL

Populating Column Headers in a ListBox
 

Harald, thanks for the quick reply. What if I do the
below? How would I then set the first row values to
populate the column headings? Thanks.

------------Code Start------------
Private Sub UserForm_Initialize()
cCounter = Sheets("DBASE").UsedRange.Columns.Count
myRange = Sheets("DBASE").UsedRange.Address
lb1.RowSource = myRange
lb1.ColumnHeads = True
lb1.ColumnCount = cCounter
End Sub
------------Code End------------

-----Original Message-----
Hi

Listbox column headers work only when a listbox'
Listfillrange / Rowsource
is set to a range of cells that has a header row. You
can't do it populating
by code.

HTH. best wishes Harald

"KL" skrev i melding
...
Hi there,

I am using the following code to populate a listbox with
multiple-column entries. My problem is I can't find a
way
to populate the columns' headers too. Any ideas muchly
appreciated.
KL


.

Populating Column Headers in a ListBox
 

Problem is that the header row should not be included in the rowsource.

This works here with row 1 as header row:

Private Sub UserForm_Initialize()
Dim R As Range
Dim L As Long, C As Long
L = Sheets("DBASE").Cells(10000, 1).End(xlUp).Row
C = Sheets("DBASE").UsedRange.Columns.Count
Set R = Sheets("DBASE").Cells(2, 1).Resize(L, C)
With ListBox1
.ColumnCount = C
.RowSource = "DBASE!" & R.Address
.ColumnHeads = True
End With
End Sub

HTH. Best wishes Harald


"KL" skrev i melding
...
Harald, thanks for the quick reply. What if I do the
below? How would I then set the first row values to
populate the column headings? Thanks.

------------Code Start------------
Private Sub UserForm_Initialize()
cCounter = Sheets("DBASE").UsedRange.Columns.Count
myRange = Sheets("DBASE").UsedRange.Address
lb1.RowSource = myRange
lb1.ColumnHeads = True
lb1.ColumnCount = cCounter
End Sub
------------Code End------------

-----Original Message-----
Hi

Listbox column headers work only when a listbox'
Listfillrange / Rowsource
is set to a range of cells that has a header row. You
can't do it populating
by code.

HTH. best wishes Harald

"KL" skrev i melding
...
Hi there,

I am using the following code to populate a listbox with
multiple-column entries. My problem is I can't find a
way
to populate the columns' headers too. Any ideas muchly
appreciated.
KL


.

Populating Column Headers in a ListBox
 

Harald,

Thanks a lot - it works nicely.

KL
-----Original Message-----
Problem is that the header row should not be included in
the rowsource.

This works here with row 1 as header row:

Private Sub UserForm_Initialize()
Dim R As Range
Dim L As Long, C As Long
L = Sheets("DBASE").Cells(10000, 1).End(xlUp).Row
C = Sheets("DBASE").UsedRange.Columns.Count
Set R = Sheets("DBASE").Cells(2, 1).Resize(L, C)
With ListBox1
.ColumnCount = C
.RowSource = "DBASE!" & R.Address
.ColumnHeads = True
End With
End Sub

HTH. Best wishes Harald


"KL" skrev i melding
...
Harald, thanks for the quick reply. What if I do the
below? How would I then set the first row values to
populate the column headings? Thanks.

------------Code Start------------
Private Sub UserForm_Initialize()
cCounter = Sheets("DBASE").UsedRange.Columns.Count
myRange = Sheets("DBASE").UsedRange.Address
lb1.RowSource = myRange
lb1.ColumnHeads = True
lb1.ColumnCount = cCounter
End Sub
------------Code End------------

-----Original Message-----
Hi

Listbox column headers work only when a listbox'
Listfillrange / Rowsource
is set to a range of cells that has a header row. You
can't do it populating
by code.

HTH. best wishes Harald

"KL" skrev i
melding
...
Hi there,

I am using the following code to populate a listbox
with
multiple-column entries. My problem is I can't find a
way
to populate the columns' headers too. Any ideas
muchly
appreciated.
KL


.



.