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

Here's a suggestion: write a class that takes two listboxes as
parameters and changes the properties of the 'headers' listbox
relative to the 'main' listbox. As you say, it required a bit of
jiggling to get right, so write that class now to save yourself the
bother of all that jiggling next time. Post it here and soak up the
kudos.

Jamie.

--

(Jamie Collins) wrote in message . com...
Here's a suggestion: write a class that takes two listboxes as
parameters and changes the properties of the 'headers' listbox
relative to the 'main' listbox. As you say, it required a bit of
jiggling to get right, so write that class now to save yourself the
bother of all that jiggling next time. Post it here and soak up the
kudos.

Jamie.

--

I had not intended to spend the time as you suggested but anyhow I've
done it!

I have not included the class module here because it is somewhat
lengthy for a posting but am happy to provide it on request.

It does not need the reference to the header listbox as the control is
created on the fly as well as adjusting the data listbox accordingly.

Suggestions for improvement welcome.

Ian

(Robots) ...

I had not intended to spend the time as you suggested but anyhow I've
done it!

I have not included the class module here because it is somewhat
lengthy for a posting but am happy to provide it on request.

I am interested. How about if I requested you to post the code in
plain text here? <g.

If you re-post with a title such as, "Repost: ListBox Headings
(warning: long post)", then I don't think you'll offend anyone. Do not
post attachments.

Thanks,
Jamie.

--