R
Robots
There have been many requests and responses regarding how to add
headings to a ListBox with multiple columns. Most assume that both
the data and headings are on a worksheet. It has been pointed out
(correctly) that it is not possible to add headings if data is to be
added programmatically, ie using the AddItem and List methods (an
oversight by MS?).
But there is an easy way, or more correctly, a workaround which avoids
using a worksheet and RowSource property - something I desired when
working with a collection class (I did not want to have to write it
first to the worksheet - ugly).
As a bonus, the heading background can be customised to give a
professional look different from the data (background color, font
color, border style). It is therefore useful even if data does come
from the spreadsheet (using RowSource property).
Wow, how can this be?!
Very simply, create another listbox (say lstHeader) for the headings!
1. Place the lstHeader directly above the data listbox (say lstData)
with borders overlapping. I have found the flat look with borderstyle
= line looks good.
2. Set both listboxes to ColumnHeads = False
3. Resize height of lstHeader to accomodate only one row
4. Set ColumnCount and ColumnWidth properties for lstHeader equal to
that of lstData. Note though that if lstData has ListStyle property =
fmListStyleOption, you must add an additional blank heading column at
the beginning of lstHeading with an appropriate width (12pt seems
about right).
5. Change the color and font properties of lstHeader as you like.
I have found this requires a bit of jiggling of the controls on the
form to get it to work and look right. But it works beautifully for
me. Hope you all find likewise.
Cheers
headings to a ListBox with multiple columns. Most assume that both
the data and headings are on a worksheet. It has been pointed out
(correctly) that it is not possible to add headings if data is to be
added programmatically, ie using the AddItem and List methods (an
oversight by MS?).
But there is an easy way, or more correctly, a workaround which avoids
using a worksheet and RowSource property - something I desired when
working with a collection class (I did not want to have to write it
first to the worksheet - ugly).
As a bonus, the heading background can be customised to give a
professional look different from the data (background color, font
color, border style). It is therefore useful even if data does come
from the spreadsheet (using RowSource property).
Wow, how can this be?!
Very simply, create another listbox (say lstHeader) for the headings!
1. Place the lstHeader directly above the data listbox (say lstData)
with borders overlapping. I have found the flat look with borderstyle
= line looks good.
2. Set both listboxes to ColumnHeads = False
3. Resize height of lstHeader to accomodate only one row
4. Set ColumnCount and ColumnWidth properties for lstHeader equal to
that of lstData. Note though that if lstData has ListStyle property =
fmListStyleOption, you must add an additional blank heading column at
the beginning of lstHeading with an appropriate width (12pt seems
about right).
5. Change the color and font properties of lstHeader as you like.
I have found this requires a bit of jiggling of the controls on the
form to get it to work and look right. But it works beautifully for
me. Hope you all find likewise.
Cheers