ListBox Questions

[AndrewKent]

Following on from my previous thread about getting a ListBox selection to populate into a cell, I have a few more questions about how a ListBox functions.

The data that appears in my ListBox needs to be variable based on the amount of rows that are populated (the data's being pulled back from Access). I have a formula setup within a defined range however this doesn't work if NO data is pulled back (and this the ListBox would be empty). The formula I'm using is this...

=OFFSET('Search Engine'!$E$4,1,0,SUM(COUNTA('Search Engine'!$E:$E),1)-1,6)

...does anyone have a good formula for a variable range row source for a list box?

also, is there any way to change the colour of the selected row from that horrible grey to something else?

...and finally...

I need to set the column widths so that they are only as wide as the longest data in the column. Is this possible?

Andy

 

[RoryA]

You can use code rather than a DNR to populate the list, or you could specify at least one row:
=OFFSET('Search Engine'!$E$4,1,0,MAX(COUNTA('Search Engine'!$E:$E)-1,1),6)
The selected row is generally blue by default in my experience - it's tied into the Windows colour scheme.
The third part is more trouble than it's worth to my mind.

 

[AndrewKent]

Thanks, Shame about the defaul colour (I have a specific colour scheme from the program I'm writing) but it's not a biggie. Thanks again though!

 

[RoryA]

I should add that you can fudge the column width thing as long as you use a fixed width font.