工具栏和菜单栏
工具栏和菜单栏的运用更多时候是伴随着加载宏和个性Excel界面的出现而出现。在不断加深对Excel VBA的理解和运用,我们编程的思路渐渐会转到考虑代码的通用性和应用方案上,将代码和Excel数据源分开。因此,制作更多具有通用功能的加载宏(不管是xla加载宏,还是Com加载宏),可以最大极限的发挥VBA编程的魅力,而不是要求用户强制启用宏。也正是因为这个原因,在我们去学习工具栏和菜单栏时,要明白的一个道理是,制作工具栏仅仅是为了加载宏等具体运用的实现,不要一味地去追求工具栏的花哨。
一、几个基本概念
在开始本节之前,先理解什么是命令栏?
命令栏(CommandBars):是工具栏、菜单栏和快捷菜单的统称。
工具栏:带有按钮和选项的工具条,使用这些按钮和选项可执行命令。如下图:
菜单栏:标题栏下的水平栏,包括菜单名称。如下图
快捷菜单:又叫弹出式菜单,鼠标右键单击。如下图。
二、CommandBars集合对象
通过上面几幅图片的直观概念之后,我们接下来理解CommandBar集合。所有的工具栏和菜单栏代码都是围绕Commandbars集合展开的。
CommandBarControls集合包含三种类型控件。
CommandBarButton:代表命令栏中的一个按钮控件(按钮控件:工具栏上的按钮,或菜单、子菜单或快捷菜单上的菜单项,当单击它们时会运行一条命令。工具栏按钮和菜单项共享相同的属性和方法。)。该控件的Type 属性必须是msoControlButton。)
CommandBarComboBox:代表命令栏中的一个组合框控件(组合框控件:菜单栏、工具栏、菜单、子菜单或快捷菜单上的自定义编辑框、下拉列表框或组合框。当工具栏垂直停靠时,它所包含的任何自定义组合框控件都不可见。)。该控件的Type 属性必须是msoControlEdit、msoControlDropdown、msoControlComboBox、msoControlButtonDropdown、msoControlSplitDropdown、msoControlOCXDropdown、msoControlGraphicCombo 或msoControlGraphicDropdown。)
CommandBarPopup:代表命令栏中的一个弹出式控件(弹出式控件:是菜单栏或工具栏上的内置或自定义控件,当单击它时显示菜单,或者是菜单、子菜单、或快捷菜单上的内置或自定义菜单项,当指针放在其上时显示子菜单。)。
该控件的Type 属性必须是msoControlPopup、msoControlGraphicPopup、msoControlButtonPopup、msoControlSplitButtonPopup 或msoControlSplitButtonMRUPopup。
几种常见属性,参数和方法:
Visible
Name
Type
Postion
Temporary
Caption
OnAction
FaceID
Style
Enable
Top/Left/Width/Hight
BeginGroup
Controls
Add方法
Findcontrols方法
下面将通过实例来解释上述属性、参数和方法的运用。
三、实例代码
1、建立一命令栏
https://www.wendangku.net/doc/8817114809.html,mandBars.Add 即建立了一个工具栏。
一般的,我们会相应的定义一个Commandbar对象来操作这个自定义工具栏,如下代码:
Sub AddCommandBar1() '添加一自定义工具栏
Dim cmdBar As CommandBar
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add
End Sub
但,Excel好像任何变化,这是因为自定义工具栏的默认Visible为False。
Sub AddCommandBar2() '添加一自定义工具栏,并显示
Dim cmdBar As CommandBar
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add
cmdBar.Visible = True
End Sub
2、Position示例
Position: 默认值为msoBarFloating
常量说明
msoBarLeft、msoBarTop、msoBarRight 和msoBarBottom 指定新命令栏的左侧、顶部、右侧和底部坐标msoBarFloating 指定新命令栏不固定
msoBarPopup 指定新命令栏为快捷菜单
msoBarMenuBar 仅适用于Macintosh 机
Sub AddCommandBar3()
Dim cmdBar As CommandBar
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add(, , , Temporary:=True)
With cmdBar
.Name = "My Bar"
.Visible = True
.Position = msoBarTop
End With
End Sub
Sub AddCommandBar4()
Dim cmdBar As CommandBar
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add(Name:="My Bar", Position:=msoBarTop, Temporary:=True) cmdBar.Visible = True
End Sub
为了避免出现重复的自定义工具栏,常规的代码写法是先删除工具栏后,再添加。
Sub AddCommandBar5()
Dim cmdBar As CommandBar
Call DeleteCommandBar
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add(Name:="My Bar", Position:=msoBarTop, Temporary:=True) cmdBar.Visible = True
End Sub
Sub DeleteCommandBar()
On Error Resume Next
https://www.wendangku.net/doc/8817114809.html,mandBars("My Bar").Delete
End Sub
3、CommandBar Controls Type示例
接下来我们介绍CommandBarControl对象
CommandBarControl对象与CommandBarButton、CommandBarComboBox 以及CommandBarPopup 对象具有同样的属性和方法.
Sub AddCmdCtlType()
Dim cmdBar As CommandBar
Dim cmdBtn As CommandBarButton
Dim cmdCombo As CommandBarComboBox
Dim cmdPop As CommandBarPopup
Call DeleteCtl
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add(Name:="CommandControl Type", Temporary:=True)
With cmdBar
.Visible = True
Set cmdBtn = .Controls.Add(Type:=msoControlButton)
With cmdBtn
.Caption = "Button"
.Style = msoButtonCaption
End With
Set cmdPop = .Controls.Add(Type:=msoControlPopup)
With cmdPop
.Caption = "Popup"
End With
Set cmdCombo = .Controls.Add(Type:=msoControlComboBox)
With cmdCombo
.Caption = "Combo"
End With
End With
End Sub
Sub DeleteCtl()
On Error Resume Next
https://www.wendangku.net/doc/8817114809.html,mandBars("CommandControl Type").Delete
End Sub
4、Width、Height示例
Sub AddButtonHight()
Dim cmdBar As CommandBar
Dim cmdBtn As CommandBarButton
Call DeleteBtn
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add(Name:="cmdBtn Type", temporary:=True) With cmdBar
.Visible = True
Set cmdBtn = .Controls.Add(Type:=msoControlButton)
With cmdBtn
.Caption = "Hight Show"
.Style = msoButtonCaption
.Height = 50
End With
End With
End Sub
Sub DeleteBtn()
On Error Resume Next
https://www.wendangku.net/doc/8817114809.html,mandBars("cmdBtn Type").Delete
On Error GoTo 0
End Sub
5、内置FaceID、OnAction和Style在CommandBarButton中的示例
Sub AddCmdButton()
Dim cmdBar As CommandBar
Dim cmdBtn As CommandBarButton
Dim cmdBtn2 As CommandBarButton
Call DeleteBtn
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars.Add(Name:="cmdBtn Type", Temporary:=True) With cmdBar
.Visible = True
Set cmdBtn = .Controls.Add(Type:=msoControlButton)
With cmdBtn
.Caption = "Button1"
.FaceId = 12
.OnAction = "ButtonShow1"
.Style = msoButtonIconAndCaption
End With
Set cmdBtn2 = .Controls.Add(Type:=msoControlButton)
With cmdBtn2
.Caption = "Button2"
.FaceId = 13
.OnAction = "ButtonShow2"
.Style = msoButtonIconAndCaption
End With
End With
End Sub
Sub ButtonShow1()
MsgBox "Button1 test"
End Sub
Sub ButtonShow2()
MsgBox "Button2 test"
End Sub
6、利用个性图案制作自己的FaceID
参考:AddCustomICO.xls
7、CommandBar Type示例:Popup
参考:CommandBar Popup1.xls、CommandBar Popup2.xls、CommandBar Popup Form.xls 8、建立一菜单栏
Sub AddMenuBar()
Dim cmdBar As CommandBar
Dim cmdMenu As CommandBarPopup
Dim cmdBtn As CommandBarButton
Call DeleteMenuBar
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars("WorkSheet Menu Bar")
'Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars(1)
With cmdBar
Set cmdMenu = .Controls.Add(Type:=msoControlPopup, temporary:=True)
With cmdMenu
.Caption = "My Menu"
Set cmdBtn = .Controls.Add(Type:=msoControlButton)
With cmdBtn
.Caption = "Item1"
.OnAction = "Item1Action"
.FaceId = 12
End With
End With
End With
End Sub
Sub Item1Action()
MsgBox "Menu Item test"
End Sub
Sub DeleteMenuBar()
On Error Resume Next
https://www.wendangku.net/doc/8817114809.html,mandBars(1).Controls("My Menu").Delete
End Sub
9、利用内置命令制作自己的菜单
Sub AddMenuBar2()
Dim cmdBar As CommandBar
Dim cmdMenu As CommandBarPopup
Dim cmdBtn As CommandBarButton
Dim cmdBuiltInBtn As CommandBarButton
Dim cmdBuiltInBtn2 As CommandBarPopup
Call DeleteMenuBar
Set cmdBar = https://www.wendangku.net/doc/8817114809.html,mandBars("WorkSheet Menu Bar")
With cmdBar
Set cmdMenu = .Controls.Add(Type:=msoControlPopup, temporary:=True)
With cmdMenu
.Caption = "My Menu"
Set cmdBtn = .Controls.Add(Type:=msoControlButton)
With cmdBtn
.Caption = "Item1"
.OnAction = "Item1Action"
.FaceId = 12
End With
Set cmdBuiltInBtn = .Controls.Add(Type:=msoControlButton, ID:=18)
Set cmdBuiltInBtn2 = .Controls.Add(10, 30017)
End With
End With
End Sub
10、恢复工具栏初始状态
参考:Reset CommandBar.xls
11、列出CommandBars & CommandControls ID & FaceID
参考:ListAllFaceID1.xls、ListAllFaceID2.xls、ListAllFaceID3.xls、ListAllFaceID-Crdotlin.xls 12、工具栏练习讲解
参考:ProtectSheet.xla