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