thisworkbook代码 Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteButton End SubPrivate Sub Workbook_Open() Call CreateButton End Sub 模块代码Public Sub CreateButton() On Error Resume Next Dim mynum As Integer, myname As String, mycom As String Dim myPosition As Variant '指定自定义工具栏的位置 myPosition = msoBarTop '指定工具栏的顶部坐标 ' myPosition = msoBarLeft '指定工具栏的左侧坐标 ' myPosition = msoBarRight '指定工具栏的右侧坐标 ' myPosition = msoBarBottom '指定工具栏的底部坐标 ' myPosition = msoBarFloating '指定工具栏的位置不固定
'创建数据分析工具栏 Application.CommandBars("数据分析").Delete Application.CommandBars.Add(Name:="数据分析", _ Position:=myPosition).Visible = True Call myButton("数据分析", "命令按钮1", 1, "命令1_1") Call myButton("数据分析", "命令按钮2", 2, "命令2_1") Call myButton("数据分析", "命令按钮3", 3, "命令3_1") End SubPublic Sub myButton(myCmd As String, myname As String, _ mynum As Integer, mycom As String) Set newButton = Application.CommandBars(myCmd).Controls.Add( _ Type:=msoControlButton, Before:=mynum) With newButton .Style = msoButtonCaption .Width = 80 .BeginGroup = True .Caption = myname .OnAction = mycom End With End SubPublic Sub DeleteButton() On Error Resume Next Application.CommandBars("数据分析").Delete On Error GoTo 0 End SubPublic Sub 命令1_1() MsgBox "单击了命令按钮 命令按钮1" End Sub Public Sub 命令2_1() MsgBox "单击了命令按钮 命令按钮2" End Sub Public Sub 命令3_1() MsgBox "单击了命令按钮 命令按钮3" End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteButton
End SubPrivate Sub Workbook_Open()
Call CreateButton
End Sub
模块代码Public Sub CreateButton()
On Error Resume Next
Dim mynum As Integer, myname As String, mycom As String
Dim myPosition As Variant
'指定自定义工具栏的位置
myPosition = msoBarTop '指定工具栏的顶部坐标
' myPosition = msoBarLeft '指定工具栏的左侧坐标
' myPosition = msoBarRight '指定工具栏的右侧坐标
' myPosition = msoBarBottom '指定工具栏的底部坐标
' myPosition = msoBarFloating '指定工具栏的位置不固定
'创建数据分析工具栏
Application.CommandBars("数据分析").Delete
Application.CommandBars.Add(Name:="数据分析", _
Position:=myPosition).Visible = True
Call myButton("数据分析", "命令按钮1", 1, "命令1_1")
Call myButton("数据分析", "命令按钮2", 2, "命令2_1")
Call myButton("数据分析", "命令按钮3", 3, "命令3_1")
End SubPublic Sub myButton(myCmd As String, myname As String, _
mynum As Integer, mycom As String)
Set newButton = Application.CommandBars(myCmd).Controls.Add( _
Type:=msoControlButton, Before:=mynum)
With newButton
.Style = msoButtonCaption
.Width = 80
.BeginGroup = True
.Caption = myname
.OnAction = mycom
End With
End SubPublic Sub DeleteButton()
On Error Resume Next
Application.CommandBars("数据分析").Delete
On Error GoTo 0
End SubPublic Sub 命令1_1()
MsgBox "单击了命令按钮 命令按钮1"
End Sub
Public Sub 命令2_1()
MsgBox "单击了命令按钮 命令按钮2"
End Sub
Public Sub 命令3_1()
MsgBox "单击了命令按钮 命令按钮3"
End Sub