Excel VBA常用技巧 第07章 菜单和工具栏.docx

1、Excel VBA常用技巧 第07章 菜单和工具栏VBA常用技巧 目录VBA常用技巧 1第7章 菜单和工具栏 2技巧1 在菜单中添加菜单项 2技巧2 在菜单栏指定位置添加菜单 5技巧3 屏蔽和删除工作表菜单 7技巧4 改变系统菜单的操作 8技巧5 定制自己的系统菜单 9技巧6 改变菜单按钮图标 15技巧7 右键快捷菜单增加菜单项 16技巧8 自定义右键快捷菜单 17技巧9 使用右键菜单制作数据有效性 20技巧10 禁用工作表右键菜单 22技巧11 创建自定义工具栏 23技巧12 自定义工具栏按钮图标 26技巧13 自定义工作簿图标 27技巧14 移除工作表的最小最大化和关闭按钮 28技巧15

2、在工具栏上添加下拉列表框 29技巧16 屏蔽工作表的复制功能 31技巧17 禁用工具栏的自定义 32技巧18 屏蔽所有的命令栏 35技巧19 恢复Excel的命令栏 36第7章 菜单和工具栏技巧1 在菜单中添加菜单项在Excel工作表的菜单中可以添加新的菜单项和子菜单，如下面的代码所示。#001 Sub myTools()#002 Dim myTools As CommandBarPopup#003 Dim myCap As Variant#004 Dim myid As Variant#005 Dim i As Byte#006 myCap = Array(基础应用, VBA程序开发, 函数

3、与公式, 图表与图形, 数据透视表)#007 myid = Array(281, 283, 285, 287, 292)#008 With Application.CommandBars(Worksheet menu bar)#009 .Reset#010 Set myTools = .Controls(帮助(&H).Controls.Add(Type:=msoControlPopup, Before:=1)#011 With myTools#012 .Caption = Excel Home 技术论坛#013 .BeginGroup = True#014 For i = 1 To 5#015

4、 With .Controls.Add(Type:=msoControlButton)#016 .Caption = myCap(i - 1)#017 .FaceId = myid(i - 1)#018 .OnAction = myC#019 End With#020 Next#021 End With#022 End With#023 Set myTools = Nothing#024 End Sub代码解析：myTools过程使用Add方法在Excel工作表菜单栏中的“帮助”菜单中添加一个标题为“Excel Home 技术论坛”的菜单项和5个子菜单。第2行到第5行代码声明变量类型。第6、7

5、行代码使用Array函数创建两个数组用于保存子菜单的名称和图标ID。第9行代码，在添加菜单项前先使用Reset方法重置菜单栏以免重复添加菜单项。Reset方法重置一个内置控件，恢复该控件原来对应的动作，并将各属性恢复成初始状态，语法如下：expression.Reset参数expression是必需的，返回一个命令栏或命令栏控件对象。第10行代码，使用Add方法在Excel工作表菜单栏中的“帮助”菜单中添加菜单项。Add方法应用于CommandBarControls对象时，新建一个CommandBarControl对象并添加到指定命令栏上的控件集合，语法如下：expression.Add(Ty

6、pe, Id, Parameter, Before, Temporary)参数expression是必需的，返回一个CommandBarControls对象，代表命令栏中的所有控件。参数Type是可选的，添加到指定命令栏的控件类型，可以为表格 791所列的MsoControlType常数之一。常数值控件类型msoControlButton1命令按钮msoControlEdit2文本框msoControlDropdown3下拉列表控制框msoControlComboBox4下拉组合控制框msoControlPopup10弹出式控件表格 791 MsoControlType常数因为在本例中将添加的

7、是带有子菜单的菜单项，所以将参数Type设置为弹出式控件。参数Id是可选的，标识整数。如果将该参数设置为 1或者忽略，将在命令栏中添加一个空的指定类型的自定义控件。参数Parameter是可选的，对于内置控件，该参数用于容器应用程序运行命令。对于自定义控件，可以使用该参数向Visual Basic过程传递信息，或用其存储控件信息。参数Before是可选的，表示新控件在命令栏上位置的数字。新控件将插入到该位置控件之前。如果忽略该参数，控件将添加到指定命令栏的末端。本例中将Before参数设置为1，菜单项添加到“帮助”菜单的顶端。参数Temporary是可选的。设置为True将使添加的菜单项为临时

8、的，在关闭应用程序时删除。默认值为False。第12行代码，设定新添加菜单项的Caption属性为“Excel Home 技术论坛”。Caption属性返回或设置命令栏控件的标题。第13行代码，设置新添加菜单项的BeginGroup属性为True，分组显示。第14行到第19行代码，在“Excel Home 技术论坛”菜单项上添加五个子菜单并设置其Caption属性、FaceId属性和OnAction属性。FaceId属性设置出现在菜单标题左侧的图标，以数字表示，一个数字代表一个内置的图标。OnAction属性设置一个VBA的过程名，该过程在用户单击子菜单时运行，本例中设置为下面的过程。#001

9、 Public Sub myC()#002 MsgBox 您选择了： & Application.CommandBars.ActionControl.Caption#003 End Sub代码解析：myC过程是单击新添加子菜单所运行过程，为了演示方便在这里只使用MsgBox函数显示所其Caption属性。删除新添加的菜单项及子菜单的代码如下所示。#001 Sub DelmyTools()#002 Application.CommandBars(Worksheet menu bar).Reset#003 End Sub代码解析：DelmyTools过程使用Reset方法重置菜单栏，删除添加的菜单

10、项及子菜单。为了在打开工作簿时自动添加菜单项，需要在工作簿的Activate事件中调用myTools过程，如下面的代码所示。#001 Private Sub Workbook_Activate()#002 Call myTools#003 End Sub为了在关闭工作簿时删除新添加的菜单项，还需要在工作簿的Deactivate事件中调用DelmyTools过程，如下面的代码所示。#001 Private Sub Workbook_Deactivate()#002 Call DelmyTools#003 End Sub如果希望这个菜单为所有工作簿使用，那么就应该在工作簿的Open事件中调用myT

11、ools过程，在BeforeClose事件中调用DelmyTools过程。运行myTools过程，将在Excel工作表菜单栏中的“帮助”菜单中添加一个名为“Excel Home 技术论坛”的菜单项及五个子菜单，如图 791所示。图 791 在“帮助”菜单中添加菜单项及子菜单技巧2 在菜单栏指定位置添加菜单除了可以在工作表菜单中添加菜单项外，还可以在工作表菜单栏的指定位置添加菜单，如下面的代码所示。#001 Sub AddNewMenu()#002 Dim HelpMenu As CommandBarControl#003 Dim NewMenu As CommandBarPopup#004 W

12、ith Application.CommandBars(Worksheet menu bar)#005 .Reset#006 Set HelpMenu = .FindControl(ID:=.Controls(帮助(&H).ID)#007 If HelpMenu Is Nothing Then#008 Set NewMenu = .Controls.Add(Type:=msoControlPopup)#009 Else#010 Set NewMenu = .Controls.Add(Type:=msoControlPopup, _#011 Before:=HelpMenu.Index)#012

13、 End If#013 With NewMenu#014 .Caption = 统计(&S)#015 With .Controls.Add(Type:=msoControlButton)#016 .Caption = 输入数据(&D)#017 .FaceId = 162#018 .OnAction = #019 End With#020 With .Controls.Add(Type:=msoControlButton)#021 .Caption = 汇总数据(&T)#022 .FaceId = 590#023 .OnAction = #024 End With#025 End With#02

14、6 End With#027 Set HelpMenu = Nothing#028 Set NewMenu = Nothing#029 End Sub代码解析：AddNewMenu过程使用Add方法在工作表“帮助”菜单前添加一个标题为“统计”的菜单和两个菜单项。第6行代码，使用FindControl方法在工作表菜单栏中查找“帮助”菜单。应用于CommandBars对象的FindControl方法返回一个符合指定条件的CommandBarControl对象。语法如下：expression.FindControl(Type, Id, Tag, Visible, Recursive)参数expres

15、sion是必需的，返回一个CommandBars对象。参数Type是可选的，要查找控件的类型。参数Id是可选的，要查找控件的标识符。参数Tag是可选的，要查找控件的标记值。参数Visible是可选，如果该值为True，那么只查找屏幕上显示的命令栏控件。默认值为False。参数Recursive是可选的，如果该值为True，那么将在命令栏及其全部弹出式子工具栏中查找。此参数仅应用于CommandBar对象。默认值为False。如果没有控件符合搜索条件，那么FindControl方法返回Nothing。第7行到第12行代码，如果工作表菜单栏中存在“帮助”菜单，将“统计”菜单添加到“帮助”菜单之前，否则添加到工作表菜单栏末尾。第12行到第25行代码，在“统计”菜单中添加两个子菜单并设置其各种属性。运行AddNewMenu过程，将在工作表菜单栏的“帮助”菜单之前添加一个“统计”菜单，如图 801所示。图 801 在工作表菜单栏中添加菜单技巧3