[VBA]Excel工具栏代码

1.定义一些全局变量

Dim Obj_Toolbar As CommandBar’代表工具栏的变量
Dim Obj_Menu As CommandBarPopup’代表菜单的变量
Dim Obj_Toolbar_button As CommandBarButton’代表菜单项和按钮的变量

2.编制生成工具栏和菜单的子程序

Sub addbutton()’创建工具栏和菜单并设置属性的子程序
_deletebutton’调用删除工具栏和菜单的子程序
Set Obj_Toolbar = Application.CommandBars.Add("My_Custom_Bar")’新建工具栏，“My_Custom_Bar”代表工具栏的名称
Set Obj_Menu = Obj_Toolbar.Controls.Add(Type:=msoControlPopup, ID:=1)’在工具栏上新建下拉菜单，“ID:=1”代表该工具栏的功能由用户自定义，下同
With Obj_Menu’设置下拉菜单的属性
.Caption = "风格切换"’设置标题
.BeginGroup = True’设置分组
End With
Set Obj_Toolbar_button = Obj_Menu.Controls.Add(Type:=msoControlButton, ID:=1)’新建菜单项，下同
With Obj_Toolbar_button’设置菜单项的属性，下同
.Caption = "标准风格"
.BeginGroup = True
.OnAction = "Standard_Style"’设置单击菜单项执行的子程序名称
End With
Set Obj_Toolbar_button = Obj_Menu.Controls.Add(Type:=msoControlButton, ID:=1)’新建其他的菜单项，并设置属性
With Obj_Toolbar_button
.Caption = "简单风格"
.BeginGroup = True
.OnAction = "Simple_Style"
End With
Set Obj_Toolbar_button = Obj_Menu.Controls.Add(Type:=msoControlButton, ID:=1)
With Obj_Toolbar_button
.Caption = "绘图和制表风格"
.BeginGroup = True
.OnAction = "Draw_Table_Style"
End With
Set Obj_Toolbar_button = Obj_Toolbar.Controls.Add(Type:=msoControlButton, ID:=1)’新建工具栏按钮
With Obj_Toolbar_button’设置按钮的属性
  .Caption = "关于"
  .Style = msoButtonIconAndCaption
  .FaceId = 984
  .OnAction = "Show_Msg"
End With
With Obj_Toolbar’设置工具栏的属性
 .Visible = True’工具栏可视
 .Enabled = True’工具栏可用
 .Position = msoBarTop’工具栏置顶
End With
Set Obj_Menu = Application.CommandBars("Menu Bar").Controls.Add(Type:=msoControlPopup, ID:=1)’在Word XP的主菜单中新建菜单，“Menu Bar”代表Word XP主菜单的名称
With Obj_Menu’设置新建菜单的属性
 .Caption = "风格切换"
End With
Set Obj_Toolbar_button = Obj_Menu.Controls.Add(Type:=msoControlButton, ID:=1)’在新建菜单中添加菜单项，下同
With Obj_Toolbar_button’设置新建菜单项的属性，下同
.Caption = "标准风格"
.BeginGroup = True
.OnAction = "Standard_Style"
End With
Set Obj_Toolbar_button = Obj_Menu.Controls.Add(Type:=msoControlButton, ID:=1)’新建其他的菜单项，并设置属性
With Obj_Toolbar_button
.Caption = "简单风格"
.BeginGroup = True
.OnAction = "Simple_Style"
End With
Set Obj_Toolbar_button = Obj_Menu.Controls.Add(Type:=msoControlButton, ID:=1)
With Obj_Toolbar_button
.Caption = "绘图和制表风格"
.BeginGroup = True
.OnAction = "Draw_Table_Style"
End With
End Sub

3.编制删除工具栏和菜单的子程序

Sub _deletebutton()’删除工具栏和菜单的子程序
Dim tempbar As CommandBar’定义临时工具栏变量
On Error Resume Next’该语句用于忽略错误
Application.CommandBars("Menu Bar").Reset’重新设置Word XP的主菜单，即删除新建的菜单
For Each tempbar In Application.CommandBars’通过“For Each…Next”语句遍历Word XP所有的工具栏
If tempbar.name = "My_Custom_Bar" Then’如名称和新建的工具栏相同
tempbar.Visible = False’设置为不可视
tempbar._delete’删除该工具栏
End If
Next
End Sub