Close all ComboBox dropdowns when I click a button.

[dagda13]

Hi, I have a sheet that contains several ComboBoxes embedded directly into the sheet. I have them set up so that when I GotFocus the ComboBox dropdown opens (.DropDown method). However, what I would like to do is clear the ComboBoxes and close the ComboBox drop downs when I click on a button. Clearing the ComboBoxes is simple --- they are linked to cells that I clear by Range. However, I have been unable to find a way to close the ComboBox drop downs. I guess I'm looking for something like a "reverse" .DropDown method, but I've been unable to find any equivalent.

 

[Yongle]

Try this

Sub Button4_Click()
    Dim objX As OLEObject
    For Each objX In ActiveSheet.OLEObjects
        With objX
            If TypeName(.Object) = "ComboBox" Then
                .Visible = False
                .Visible = True
            End If
        End With
    Next
End Sub

 

[DanteAmor]

If you select a cell, does the dropdown close?

 

[dagda13]

Thanks Yongle, it works!

 

[Yongle]

Thanks for the feedback