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?

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).

--
Regards,
Tom Ogilvy


"Tim Coddington" wrote in message
...
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?

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

Vasant

"Tim Coddington" wrote in message
...
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?

Setting the rowsource to "" works for me.

--
Regards,
Tom Ogilvy

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
I don't believe you can clear a listbox bound to a range without clearing
the source range.
.
--

Vasant

"Tim Coddington" wrote in message
...
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?

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

Regards,

Vasant.

"Tom Ogilvy" wrote in message
...
Setting the rowsource to "" works for me.

--
Regards,
Tom Ogilvy

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
I don't believe you can clear a listbox bound to a range without
clearing
the source range.
.
--

Vasant

"Tim Coddington" wrote in message
...
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?

Hey! That helped out a great deal. Couldn't think what I'd
changed to save my soul, but I guess that was it!
"keepITcool" wrote in message
...
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 :

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?

"Tom Ogilvy" wrote in message
...
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).

--
Regards,
Tom Ogilvy


"Tim Coddington" wrote in message
...
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?