Listbox 3

[Tim Coddington]

OK. I give up. I have a combobox where the data is sorted and bound to an
excel range like:
Rows(RowStart% & ":" & RowEnd%).Sort Key1:=Range(Column$ &
RowStart%), Header:=xlYes
.RowSource = Column$ & RowStart% & ":" & Column$ & RowEnd%
.ListRows = IIf(.ListCount > 20, 20, .ListCount)
.DropDown
The above occurs on the selection of the combobox via ._Enter.
When the user selects an item, I record the selection and clear
<unsuccessfully> the combobox .
I have tried .clear, but that only is supposed to be used with .Additem.
And the system tells
me so; it crashes with an unspecified error.
I've tried .RowSource = "", but apparently, when you do that, .ListIndex
becomes invalid and
causes another error, and if I try to set it to -1, another error yet.
What is the proper method for clearing a listbox bound to a spreadsheet
range? Is there
something I have missed?

 

[Tom Ogilvy]

Are you trying to remove the list from the combobox or just alter it so
nothing is selected?

If the latter, then use the click event and set the listindex to -1 (don't
alter the list/rowsource).

 

[Vasant Nanavati]

I don't believe you can clear a listbox bound to a range without clearing
the source range.
..

 

[Tom Ogilvy]

Setting the rowsource to "" works for me.

 

[Vasant Nanavati]

Thanks, Tom ... that was my recollection but I didn't test it when the OP
said it didn't work. My bad .

Regards,

Vasant.

 

[Tim Coddington]

Tried setting listindex to -1. When I tried to exit the object on the
screen,
it gives me an 'Invalid Property' error, and I had assumed I couldn't set
listindex to -1. But if it wasn't that, I don't see anything else that may
have
caused my 'Invalid Property' error.

All I'm really trying to do is keep the _Change() from firing a second time.

You really can set .RowSource to one range of rows (in a column) and
then come back and set it to another range of rows later, can't you?

 

[keepITcool]

could I add following

i think this error occurs in comboboxes where the property

matchrequired = true

make sure the user cant type anything by setting the

style = to fmStyleDropDowLIST


and in your code be sure not to assign stuff to the value property,
(unless you're sure the value exists), but prefarably use the listindex.

HTH
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam



Tim Coddington wrote :

 

[Tim Coddington]

Hey! That helped out a great deal. Couldn't think what I'd
changed to save my soul, but I guess that was it!