delete Combo Box (created through ActiveX)

[krrishkrsna]

i am placing below code in ThisWorkbook (Excel VBA). whenever i go to new sheet the ComboBox1 should get created if it does not exist and should be deleted if already exists and create new combobox1, but getting below error

Run Time Error -2147024809(80070057) The item with the specified name was not found

If Not ActiveSheet.Shapes("ComboBox1").Name = "ComboBox1" Then
ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", link:=False, displayasicon:=False, Left:=100, Top:=50, _
Width:=120, Height:=28.5).Select
Else
ActiveSheet.Shapes("ComboBox1").Delete
End If

Please HELP

 

[DanteAmor]

If Not ActiveSheet.Shapes("ComboBox1").Name = "ComboBox1" Then

You can't reference an object if it doesn't exist, so it sends the error.
I show you 2 ways to do it, the first is to look for the combo, if it exists, delete it.

Sub createcombobox()
  Dim sp As Shape
  
  For Each sp In ActiveSheet.Shapes
    If sp.Name = "ComboBox1" Then
      ActiveSheet.Shapes("ComboBox1").Delete
    End If
  Next
  ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", link:=False, displayasicon:=False, Left:=100, Top:=50, _
    Width:=120, Height:=28.5).Select
End Sub

Or just remove it, if it exists remove it, if it doesn't exist with the On Error instruction, it will go to the next instruction and don't send the error.

Sub createcombobox_2()
  On Error Resume Next
  ActiveSheet.Shapes("ComboBox1").Delete
  On Error GoTo 0
  
  ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", link:=False, displayasicon:=False, Left:=100, Top:=50, _
    Width:=120, Height:=28.5).Select
End Sub

 

[krrishkrsna]

You can't reference an object if it doesn't exist, so it sends the error.
I show you 2 ways to do it, the first is to look for the combo, if it exists, delete it.

Sub createcombobox()
  Dim sp As Shape
 
  For Each sp In ActiveSheet.Shapes
    If sp.Name = "ComboBox1" Then
      ActiveSheet.Shapes("ComboBox1").Delete
    End If
  Next
  ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", link:=False, displayasicon:=False, Left:=100, Top:=50, _
    Width:=120, Height:=28.5).Select
End Sub

Or just remove it, if it exists remove it, if it doesn't exist with the On Error instruction, it will go to the next instruction and don't send the error.

Sub createcombobox_2()
  On Error Resume Next
  ActiveSheet.Shapes("ComboBox1").Delete
  On Error GoTo 0
 
  ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", link:=False, displayasicon:=False, Left:=100, Top:=50, _
    Width:=120, Height:=28.5).Select
End Sub

procedure CreateComboBox works perfectly (procedure CreateComboBox_2, throws the error which i mentioned)

googled for code and tried all those but failed. finally u helped me. thank your very much