Control Toolbox ComboBox: Clear All cb when Worksheet Opens?

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:

Dim obj as OLEObject
For Each obj in Sheet1.OLEObjects
obj.clear
Next

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

Thanks!

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 = "".

Try this and see if it works:

Dim obj As OLEObject
For Each obj In Sheet1.OLEObjects
    If TypeName(obj) = "ComboBox" Then
        obj.Clear
    Else
    End If
Next obj

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.

Try this.

For Each obj In Sheet1.OLEObjects
    obj.Object.Clear
Next obj

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

Originally Posted by Help

Clear Method (Microsoft Forms)

Removes all objects from an object or collection

....

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

The easy way is as follows

Private Sub Workbook_Open()
    ActiveSheet.CheckBox1.Value = True
    ActiveSheet.CheckBox2.Value = True
    ...
End Sub

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!

Dim oShape As Shape
    
    For Each oShape In ActiveSheet.Shapes
        Debug.Print oShape.Name
    Next

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.

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:

' A textbox is not an Excel object but an OLE one.
' There is a collection of all these in the sheets collection and we use that

Dim oleObj As OLEObject

For Each oleObj In ActiveSheet.OLEObjects
    If oleObj.OLEType = xlOLEControl Then                        ' it is a Control Tool but 
        If Left(oleObj.progID, 15) = "Forms.ComboBox." Then ' is it a combobox? 
            Set oleTxtbox = oleObj.Object                           ' get the object (generic as it's not a native Excel object)
            oleTxtbox.Value = ""                                        ' and clear it
        End If
    End If
Next

I hope that helps.