VBA动态生成按扭及其对应的事件

Public Sub MakeButton()
'动态生成一个按钮控件
'动态生成事件
'
Dim i As Integer
Dim strName As String
Dim varstrName() As String
    Dim WSheet As Worksheet
    Dim MyNewbtn As OLEObject
    Dim Target As Range
    Dim ShtCodeName As String
    Dim linesCount As Long
    Set WSheet = Sheets("Sheet4")
    ShtCodeName = WSheet.CodeName
    WSheet.OLEObjects.Delete
    Set Target = Cells(15, 2)
    strName = "Sheet1;Sheet2;Sheet3"
    varstrName = Split(strName, ";")
    linesCount = ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule.CountOfLines
    ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule.DeleteLines 1, linesCount
For i = 1 To 3
    Set MyNewbtn = WSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=Target.Left + (i * 90), Top:=Target.Top, Width:=90, Height:= _
        30)
    MyNewbtn.Name = "MyNewButton" & i '设置按钮名
    MyNewbtn.Object.Caption = "我的按钮" & i '设置按钮标题
    
    '2、如果出现"不信任到Visual Basic Project 的程序连接"的错误，解决方法为：
    '   打开Excel－》工具－》宏－》安全性－》可靠发行商，选中“信任对于Visiual Basic 项目的访问”，按确定即可。
'ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule.DeleteLines 1
    With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule
        .InsertLines 1 + ((i - 1) * 4), "Private Sub MyNewButton" & CStr(i) & "_Click()"
        .InsertLines 2 + ((i - 1) * 4), "Sheets(""" & varstrName(i - 1) & """).Activate'msgbox ""生成事件成功"""
        .InsertLines 3 + ((i - 1) * 4), "'这是一个注释示例"
        .InsertLines 4 + ((i - 1) * 4), "End Sub"
    End With
Next i
End Sub