[RESOLVED] [Excel 2003] List box (pain)
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?
Re: [Excel 2003] List box (pain)
Quote:
Originally Posted by 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?
Use a ListView instead of a listbox and it will show you the headers...
Re: [Excel 2003] List box (pain)
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)
Re: [Excel 2003] List box (pain)
Quote:
Originally Posted by 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)
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...
Re: [Excel 2003] List box (pain)
vb Code:
listbox1.columnheads = true
Re: [Excel 2003] List box (pain)
Quote:
listbox1.columnheads = true
Yes that too :)
Thanks West. How could I forget it :lol:
Re: [Excel 2003] List box (pain)
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) ;)
Re: [Excel 2003] List box (pain)
Quote:
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...
Re: [Excel 2003] List box (pain)
In that case the headers can go do one then - useless... crap 'feature'.
Thanks for your help :)
Re: [Excel 2003] List box (pain)
Quote:
Originally Posted by Ecniv
In that case the headers can go do one then - useless... crap 'feature'.
Thanks for your help :)
:lol: