Combobox.clear

[Guest]

I have several comboboxes on a worksheet, in pairs. When Box1 is changed, Box2 is populated with the Additem property, depending on box1's selection. When Box1 is altered from the original selection, the old values for Box2 need to be cleared but I cannot use Clear method, it's giving an "Unspecified error." My comboboxes are on a worksheet. Can someone help me clear the previous options of dropdownlist associated with the combobox? I really appreciate your help!

Chri

(This message is closely related to one posted by Shilps on 4/19 - subject: How to clear the contents of COmboBox?, but he never really followed up to help resolve)

 

[Guest]

try a combination of
ListCount and a RemoveItem Loo

With ComboBox
x = .ListCoun
For y = 0 To x -
.RemoveItem (y
Nex
End Wit

----- Chris Houchin wrote: ----

I have several comboboxes on a worksheet, in pairs. When Box1 is changed, Box2 is populated with the Additem property, depending on box1's selection. When Box1 is altered from the original selection, the old values for Box2 need to be cleared but I cannot use Clear method, it's giving an "Unspecified error." My comboboxes are on a worksheet. Can someone help me clear the previous options of dropdownlist associated with the combobox? I really appreciate your help!

Chri


(This message is closely related to one posted by Shilps on 4/19 - subject: How to clear the contents of COmboBox?, but he never really followed up to help resolve)

 

[Tom Ogilvy]

going forward won't work because you pass the listcount on the way up as it
comes down.

With ComboBox1
x = .ListCount
For y = x-1 To 0 Step - 1
.RemoveItem y
Next
End With

or if you want to go forward, always remove the first item (item 0)

With ComboBox1
x = .ListCount
For y = 0 To x - 1
.RemoveItem 0
Next
End With


--
Regards,
Tom Ogilvy

Thanks for the tip, Chris; however, the function is returning another error. The box says
"Run-time error '-2147467259 (80004005)':
Automation Error
Unspecified Error"
The error occurs on the " .RemoveItem (Y)" line, as if the combobox is

locked from editing (although the worksheet is unprotected). Any other
suggestions?

----- chris wrote: -----

try a combination of:
ListCount and a RemoveItem Loop

With ComboBox1
x = .ListCount
For y = 0 To x - 1
.RemoveItem (y)
Next
End With

----- Chris Houchin wrote: -----

I have several comboboxes on a worksheet, in pairs. When Box1

is changed, Box2 is populated with the Additem property, depending on box1's
selection. When Box1 is altered from the original selection, the old values
for Box2 need to be cleared but I cannot use Clear method, it's giving an
"Unspecified error." My comboboxes are on a worksheet. Can someone help me
clear the previous options of dropdownlist associated with the combobox? I
really appreciate your help!!

Chris


(This message is closely related to one posted by Shilps on

4/19 - subject: How to clear the contents of COmboBox?, but he never really
followed up to help resolve)

 

[Guest]

thx for correction Tom

----- Tom Ogilvy wrote: ----

going forward won't work because you pass the listcount on the way up as i
comes down

With ComboBox
x = .ListCoun
For y = x-1 To 0 Step -
.RemoveItem
Nex
End Wit

or if you want to go forward, always remove the first item (item 0

With ComboBox
x = .ListCoun
For y = 0 To x -
.RemoveItem
Nex
End Wit


--
Regards
Tom Ogilv

Thanks for the tip, Chris; however, the function is returning anothe error. The box say
"Run-time error '-2147467259 (80004005)'
Automation Erro
Unspecified Error
The error occurs on the " .RemoveItem (Y)" line, as if the combobox i

locked from editing (although the worksheet is unprotected). Any othe
suggestions

x = .ListCoun
For y = 0 To x -
.RemoveItem (y
Nex
End Wit

is changed, Box2 is populated with the Additem property, depending on box1'
selection. When Box1 is altered from the original selection, the old value
for Box2 need to be cleared but I cannot use Clear method, it's giving a
"Unspecified error." My comboboxes are on a worksheet. Can someone help m
clear the previous options of dropdownlist associated with the combobox?
really appreciate your help!4/19 - subject: How to clear the contents of COmboBox?, but he never reall
followed up to help resolve

 

[Tom Ogilvy]

I ran your Combobox3_change

event in a userform and it didn't have a problem. I suspect it is a context
problem - the situation when the code is run causes the problem. Based a
"light" review of all the code you provided, I really can't diagnose where
the problem might be.

--
Regards,
Tom Ogilvy

Thanks for the reply - however, I'm still getting the same "Unspecified

Error." It always occurs on the .RemoveItem line. The weird part is that I
have two identical sets of comboboxes and the first set works as desired. I
included my code below - combobox1 changes combobox2 and box3 changes box4.
The code appears identical to me (I included my previous attempt, commented
out), but an error occurs at the ".RemoveItem" line still. I would
appreciate any other ideas! I am pretty new to this stuff and can't get
this attempt off the ground!!

 

[Dick Kusleika]

Chris

You're likely getting that error because you have something in the
ListFillRange property of the combobox. You can populate a combobox with
ListFillRange or AddItem, but not both. If you use ListFillRange, AddItem,
RemoveItem, and Clear will give you that error (or similar). If the
combobox is on a Userform (as opposed to a Worksheet), the property is
called RowSource.