在VBA外部生成ActiveX命令按钮并为其分配代码

Question

我正在处理一个需要动态生成ActiveX命令按钮并自动分配某些代码的任务。每次我关闭工作表时，命令按钮需要被删除(它已完成)，但该按钮的代码保留。

所以我试图找到代码，这将分配一个代码到命令按钮，同时检查是否存在相同的子，如果它存在，然后删除它，并创建具有相同名称的新子，我需要的代码。

我的代码是

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只需以编程方式添加代码到命令按钮并删除先前的同名代码Will do also

Answer 1

感谢@funthomas的建议。根据你的建议，我试过添加简单的按钮和OnAction命令，它也简化了我的代码(我不再需要为此创建新的子)

我将分享我修改过的代码

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

我删除了主代码末尾的所有按钮，也没有为按钮创建任何新的子(多亏了OnAction的建议)，所以我不需要检查重复的子。