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
|