Sub MakeButton() '自动添加一个Sheet '动态生成一个按钮控件 '动态生成事件 ' Dim WSheet As Worksheet Dim MyNewbtn As OLEObject Dim Target As Range Dim ShtCodeName As String Set WSheet = Worksheets.Add(After:=ActiveSheet) 'WSheet.Name = "新表" Set Target = Cells(15, 7) ' Set MyNewbtn = WSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _ , DisplayAsIcon:=False, Left:=Target.Left, Top:=Target.Top, Width:=92.25, Height:= _ 30) MyNewbtn.Name = "MyNewButton" '设置按钮名 MyNewbtn.Object.Caption = "我的按钮" '设置按钮标题 ShtCodeName = WSheet.CodeName '也可以是 'ShtCodeName = ThisWorkbook.Worksheets("新表").CodeName ' '向新表中添加事件 '注意: '1、添加事情的程序名一定要与添加的按钮名称对应 '2、如果出现"不信任到Visual Basic Project 的程序连接"的错误,解决方法为: ' 打开Excel-》工具-》宏-》安全性-》可靠发行商,选中“信任对于Visiual Basic 项目的访问”,按确定即可。
With ThisWorkbook.VBProject.VBComponents.Item(ShtCodeName).CodeModule .InsertLines 1, "Private Sub MyNewButton_Click()" .InsertLines 2, "msgbox ""生成事件成功""" .InsertLines 3, "'这是一个注释示例" .InsertLines 5, "End Sub" End With End Sub
|