在VBA中生成ActiveX命令按钮并将代码分配给它们

Question

我正在研究一个任务，其中它需要动态生成ActiveX命令按钮并自动为其分配某些代码。每次关闭工作表时，都需要删除命令按钮（已完成），但是该按钮的代码仍保留。

因此，我正在尝试查找将代码分配给命令按钮的代码，然后检查是否存在相同的子，然后将其删除并创建具有所需代码的相同名称的新sub。

我的代码是

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

或只是以编程为命令按钮添加代码，然后删除先前的同名代码也会做

Answer 1

感谢@funthomas的建议。根据您的建议，我尝试将简单的按钮和gonaction命令添加到它，并且它也简化了我的代码（我不再需要为此创建新的子），

我将共享代码i i修改后，

   For i = 1 To UBound(arrFile)
    strtRow = 29
    Set ParamWS = ThisWorkbook.Worksheets("Sheet1")
    ' My other code is here to print data starting from Row 29(each row in each loop)

    Set rng = ParamWS.Range("C" & strtRow)
    p = strtRow - 28
    Set ShButton = ParamWS.Buttons.Add(Left:=43.5, Top:=rng.Top + 10, Width:=92, Height:=46)

    With ShButton
      .OnAction = "Module3.create_sheetFromButton"
      .Caption = "Single Button" & p
      .Name = strtRow
    End With

    strtRow = strtRow + 1

   Next i

我在主代码末尾删除了所有按钮，也没有为按钮创建任何新的子（得益于oncation的建议），所以我不知道需要检查重复的子。