工具栏和菜单栏

工具栏和菜单栏 [VBA] 工具栏和菜单栏 2008年06月16日 14:11:13 作者: Petery 一、几个基本概念 在开始本节之前，先理解什么是命令栏, 命令栏(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、 建立一命令栏 Application.CommandBars.Add 即建立了一个工具栏。 一般的，我们会相应的定义一个Commandbar对象来操作这个自定义工具栏，如下代码: Sub AddCommandBar1() '添加一自定义工具栏 Dim cmdBar As CommandBar Set cmdBar = Application.CommandBars.Add End Sub 但，Excel好像任何变化，这是因为自定义工具栏的默认Visible为False。 Sub AddCommandBar2() '添加一自定义工具栏,并显示 Dim cmdBar As CommandBar Set cmdBar = Application.CommandBars.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 = Application.CommandBars.Add(, , , Temporary:=True) With cmdBar .Name = "My Bar" .Visible = True .Position = msoBarTop End With End Sub Sub AddCommandBar4() Dim cmdBar As CommandBar Set cmdBar = Application.CommandBars.Add(Name:="My Bar", Position:=msoBarTop, Temporary:=True) cmdBar.Visible = True End Sub 为了避免出现重复的自定义工具栏，常规的代码写法是先删除工具栏后，再添加。 代码: Sub AddCommandBar5() Dim cmdBar As CommandBar Call DeleteCommandBar Set cmdBar = Application.CommandBars.Add(Name:="My Bar", Position:=msoBarTop, Temporary:=True) cmdBar.Visible = True End Sub Sub DeleteCommandBar() On Error Resume Next Application.CommandBars("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 = Application.CommandBars.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 Application.CommandBars("CommandControl Type").Delete End Sub 4、 Width、Height示例 代码: Sub AddButtonHight() Dim cmdBar As CommandBar Dim cmdBtn As CommandBarButton Call DeleteBtn Set cmdBar = Application.CommandBars.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 Application.CommandBars("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 = Application.CommandBars.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 = Application.CommandBars("WorkSheet Menu Bar") 'Set cmdBar = Application.CommandBars(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 Application.CommandBars(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 = Application.CommandBars("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