Form Control Combobox to clear

[osy25]

Hi,
Not a professional, just program for my self.
I have a combobox on a sheet without activeX. I would like to have a button to clear the selection. Using VBA i found that this combobox is refereed to DropDown... Under Button click i have
Sheet1.DropDown1.Clear

Unfortunately this code does not work.
How do i clear the selection via VBA?
Thank you!

 

[DanteAmor]

Hi and welcome to MrExcel

- Mouse over your Combo Box and right-click
- Choose Format Control – to popup Format Control Dialog box
- Go to the Control tab.
- Type “E4” in the Cell link input box.
- Press OK

Then you delete the value in the cell, for example:

Range("E4").clear

That way you delete the contents of the combobox.

 

[osy25]

Thank you very much for the reply.
However my question concerns the text in the combobox itself, not the cell link.

 

[DanteAmor]

Try this. The following also clears the combobox text

Change AZ1 for a cell that you have available

Sub Clear_Combo()
  Dim acell As Range
 
  Set acell = ActiveCell
  ActiveSheet.Shapes("DropDown1").Select
  With Range("AZ1")
    Selection.LinkedCell = .Address
    .Clear
    Selection.LinkedCell = ""
  End With
  acell.Activate
End Sub

 

[osy25]

Is an inserted form control a shape?

 

[DanteAmor]

Is an inserted form control a shape?

Yes

The formControl combobox has very limited properties, I suggest you change it to an ActiveX combobox. Then you use:

Sub ClearComboActiveX()
  ActiveSheet.ComboBox1.Value = ""
End Sub

 

[osy25]

Thank you for the solution.

 

[Artik]

Another solution for DropDown. Can also be for a control without a linked cell.

Sub ClearDD()
    Dim DD As DropDown
    
    Set DD = ActiveSheet.DropDowns("Drop Down 1") 'or ActiveSheet.DropDowns(1)
    
    DD.Value = 0
    
End Sub

Artik