自定义Excel-CommandBar工具栏

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