[RESOLVED] [Excel 2003] List box (pain)

[Ecniv]

Hi,

I hope some one can help and has done this before...

I would like to use a list box to show data from an array (data from an Access database).

Dumping data to the array - done....
Putting column headers... stumped.

In access with its list box, the first row (0) becomes the header. Understandable and works.
However if you fill the list box in Excel with Add Iteem and .List or .Column the first line is the first line of data. The headers remain blank.

Is there a fast way to get this working?

[Siddharth Rout]

Hi,

I hope some one can help and has done this before...

I would like to use a list box to show data from an array (data from an Access database).

Dumping data to the array - done....
Putting column headers... stumped.

In access with its list box, the first row (0) becomes the header. Understandable and works.
However if you fill the list box in Excel with Add Iteem and .List or .Column the first line is the first line of data. The headers remain blank.

Is there a fast way to get this working?

Use a ListView instead of a listbox and it will show you the headers...

[Ecniv]

1) thanks - is it a normal control? if not - I cannot use it.
2) Not the point - if the list box claims it has headers, how come it won't let you set them.

Any advice appreciated (sorry for the delay in responding but I have been away for a week)

[Siddharth Rout]

1) thanks - is it a normal control? if not - I cannot use it.
2) Not the point - if the list box claims it has headers, how come it won't let you set them.

Any advice appreciated (sorry for the delay in responding but I have been away for a week)

1) What do you mean by normal controls?
2) yes you can set the headers in a listbox

Here is an example

Type anything in cells A2:C15

In Cell A1 type "Head1"
In Cell B1 type "Head2"
In Cell C1 type "Head3"

Now go to the form where the listbox is

In the properties changes these

BoundColumn: 3
ColumnCount: 3
RowSource: Sheet1!A2:C15

Now run the form

You will see that the headers of the listbox has been set to the values for A1 to C1

Hope this helps...

[westconn1]

listbox1.columnheads = true

[Siddharth Rout]

listbox1.columnheads = true

Yes that too

Thanks West. How could I forget it

[Ecniv]

thanks

but...
westconn1:
listbox1.columnheads = true

is the same as setting the listbox to HasHeaders=true (properties)

Koolsid:
1) What do you mean by normal controls?
The default you get in Excel 2003 (no installs or extras)

2) yes you can set the headers in a listbox
Uh well. I am not using a range. I am reading in values (usually from a recordset, sometimes from a file). If you use the Add Items method you can't use the headers. the add items to row 0 is the first row of data, negative numbers cause an error (I believe).

If you can have the hasheaders/column head = true and you are allowed to add items, how come they aren't the headers?
(possibly an MS bug - might just be I am thick)

[Siddharth Rout]

yes you can set the headers in a listbox
Uh well. I am not using a range. I am reading in values (usually from a recordset, sometimes from a file). If you use the Add Items method you can't use the headers. the add items to row 0 is the first row of data, negative numbers cause an error (I believe).

The only way I can think of is to copy the data from the recordset to a blank sheet in lieu of an array and then follow the example that I mentioned above... By the way did you try the above example?

Unfortunately you cannot directly amend to the column headers. It is automatically picked up by the system...

[Ecniv]

In that case the headers can go do one then - useless... crap 'feature'.

Thanks for your help

[Siddharth Rout]

In that case the headers can go do one then - useless... crap 'feature'.

Thanks for your help