Control Toolbox ComboBox: Clear All cb when Worksheet Opens?

[Agimcomas]

Hello.

Is it possible to clear the value and the item list of all comboboxes when the Workbook is opened? I have tried without success using an object such as this:

Please Login or Register  to view this content.

An error message pops up saying that the object does not support the method or property.

Thanks!

[brynbaker]

It may not be as simple as that. I'm away from Excel on this computer so I'll have to guess.
Assuming the loop is sound, and that the only OLEobjects are the comboboxes (i.e. no buttons), I think it would be obj.text = "".

[g8r777]

Try this and see if it works:

Please Login or Register  to view this content.

[Agimcomas]

Hello.

brynbaker
It doesn't work. I get the message "Object doesn't support this property or method". Same happened with "Obj.Clear".


g8r777
It doesn't work. There is no error, but the ComboBoxes are not Cleared. I guess because of the Else Statement, meaning that TypeName(obj) <> "ComboBox".

I found this in the Help file:

For a Controls collection, Clear deletes controls that were created at run time with the Add method. Using Clear on controls created at design time causes an error.

If the control is bound to data, the Clear method fails.

I still don<t know what to do.

Thanks.

[Norie]

Try this.

Please Login or Register  to view this content.

By the way, the Clear method you posted the help for is for the Controls collection.

Clear Method (Microsoft Forms)

Removes all objects from an object or collection

....

For a ListBox or ComboBox, Clear removes all entries in the list.

[brynbaker]

The easy way is as follows

Please Login or Register  to view this content.

Of course if you have complications (and who contacts a forum unless they do), a more elegant solution is desirable. But as I say, this is simple.
If you have lots of comboxes you could even write a macro to generate the code.

What we need, as you first posted is a loop; this works fine but only gets us so far as it does not tell us what we want!

Please Login or Register  to view this content.

Putting the name somewhere else would facilitate writing a list off macro commands for the first suggestion above. Unless you have thousands of checkboxes, make progress this way.

The next refinement is to understand the object model. The Shapes collection manages common attribute of other objects it holds objects. I've been struggling recently with lack or or unhelpfully structured model documentation. Perhaps a person with 'tall shoulders' can advise.

[brynbaker]

I was about to post some more and carefully re-read your post. Some of my previous input was wrong due to a misreading and I apologies for any confusion.
I think this will be more helpful:

Please Login or Register  to view this content.

I hope that helps.