I’m working on a task where it needs to generate ActiveX Command Buttons dynamically and assign certain code to them automatically. Each time I close the Worksheet, the command button needs to be deleted (it is completed), but code for that button remains.

So I’m trying to find code which will assign a code to the command button while checking if same sub exists, if it exists then delete it and create new sub with same name having the code I need.

My code is

Public WS As Worksheet


Sub MyButton()

Dim j, p, q As Integer
Dim ShButton As OLEObject
Dim rng As Range
Dim Code As String

Set WS = ThisWorkbook.Worksheets("Sheet1")

j = 0
p = 1
q = 3

For j = 29 To WS.Cells(Rows.Count, "E").End(xlUp).Row
    Set rng = WS.Range("C" & j)

    Set ShButton = WS.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, _
            Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.RowHeight * 3 / 4)
    
    WS.OLEObjects("CommandButton" & q).Object.Caption = "My Button " & p
    WS.OLEObjects("CommandButton" & q).Object.WordWrap = True
    
    
    ' ##Need code to Check Duplicate sub and delete it if exists

    Code = ""
    Code = "Private Sub CommandButton" & q & "_Click()" & vbCrLf
    Code = Code & "MsgBox " & Chr(34) & "Worksheet name is " & Chr(34) & " & ActiveSheet.Name" & vbCrLf  ' ##Demo Code
    Code = Code & "End Sub"
    Debug.Print Code
    
    With ActiveWorkbook.VBProject.VBComponents(Worksheets("Sheet1").CodeName).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With
    
    
    
    
    p = p + 1
    q = q + 1
Next j

End Sub

OR Simply to add a code to command button programmatically and delete the previous same name code will do also