1、VBA 常用技巧代码解析 1VBA 常用技巧目录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 移除工
2、作表的最小最大化和关闭按钮 .28技巧 15 在工具栏上添加下拉列表框 .29技巧 16 屏蔽工作表的复制功能 .31技巧 17 禁用工具栏的自定义 .32技巧 18 屏蔽所有的命令栏 .35技巧 19 恢复 Excel 的命令栏 .36VBA 常用技巧代码解析 2第 7 章 菜单和工具栏技巧 1 在菜单中添加菜单项在 Excel 工作表的菜单中可以添加新的菜单项和子菜单，如下面的代码所示。#001 Sub myTools()#002 Dim myTools As CommandBarPopup#003 Dim myCap As Variant#004 Dim myid As Variant#
3、005 Dim i As Byte#006 myCap = Array(“基础应用“, “VBA 程序开发“, “函数与公式“, “图表与图形“, “数据透视表“)#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 .Ca
4、ption = “Excel Home 技术论坛“#013 .BeginGroup = True#014 For i = 1 To 5#015 With .Controls.Add(Type:=msoControlButton)#016 .Caption = myCap(i - 1)#017 .FaceId = myid(i - 1)#018 .OnAction = “myC“#019 End With#020 NextVBA 常用技巧代码解析 3#021 End With#022 End With#023 Set myTools = Nothing#024 End Sub代码解析：myToo
5、ls 过程使用 Add 方法在 Excel 工作表菜单栏中的“帮助”菜单中添加一个标题为“Excel Home 技术论坛” 的菜单项和 5 个子菜单。第 2 行到第 5 行代码声明变量类型。第 6、7 行代码使用 Array 函数创建两个数组用于保存子菜单的名称和图标 ID。第 9 行代码，在添加菜单项前先使用 Reset 方法重置菜单栏以免重复添加菜单项。Reset 方法重置一个内置控件，恢复该控件原来对应的动作，并将各属性恢复成初始状态，语法如下：expression.Reset参数 expression 是必需的，返回一个命令栏或命令栏控件对象。第 10 行代码，使用 Add 方法在 E
6、xcel 工作表菜单栏中的“帮助”菜单中添加菜单项。Add 方法应用于 CommandBarControls 对象时，新建一个 CommandBarControl 对象并添加到指定命令栏上的控件集合，语法如下：expression.Add(Type, Id, Parameter, Before, Temporary)参数 expression 是必需的，返回一个 CommandBarControls 对象，代表命令栏中的所有控件。参数 Type 是可选的，添加到指定命令栏的控件类型，可以为 表格 79-1 所列的MsoControlType 常数之一。常数 值 控件类型msoControlBu
7、tton 1 命令按钮msoControlEdit 2 文本框msoControlDropdown 3 下拉列表控制框msoControlComboBox 4 下拉组合控制框msoControlPopup 10 弹出式控件表格 1-1 MsoControlType 常数因为在本例中将添加的是带有子菜单的菜单项，所以将参数 Type 设置为弹出式控件。参数 Id 是可选的，标识整数。如果将该参数设置为 1 或者忽略，将在命令栏中添加一个空的指定类型的自定义控件。VBA 常用技巧代码解析 4参数 Parameter 是可选的，对于内置控件，该参数用于容器应用程序运行命令。对于自定义控件，可以使用该
8、参数向 Visual Basic 过程传递信息，或用其存储控件信息。参数 Before 是可选的，表示新控件在命令栏上位置的数字。新控件将插入到该位置控件之前。如果忽略该参数，控件将添加到指定命令栏的末端。本例中将 Before 参数设置为1，菜单项添加到 “帮助”菜单的顶端。参数 Temporary 是可选的。设置为 True 将使添加的菜单项为临时的，在关闭应用程序时删除。默认值为 False。第 12 行代码，设定新添加菜单项的 Caption 属性为“Excel Home 技术论坛” 。Caption 属性返回或设置命令栏控件的标题。第 13 行代码，设置新添加菜单项的 BeginGr
9、oup 属性为 True，分组显示。第 14 行到第 19 行代码，在“Excel Home 技术论坛”菜单项上添加五个子菜单并设置其 Caption 属性、FaceId 属性和 OnAction 属性。FaceId 属性设置出现在菜单标题左侧的图标，以数字表示，一个数字代表一个内置的图标。OnAction 属性设置一个 VBA 的过程名，该过程在用户单击子菜单时运行，本例中设置为下面的过程。#001 Public Sub myC()#002 MsgBox “您选择了： “ & Application.CommandBars.ActionControl.Caption#003 End Sub代
10、码解析：myC 过程是单击新添加子菜单所运行过程，为了演示方便在这里只使用 MsgBox 函数显示所其 Caption 属性。删除新添加的菜单项及子菜单的代码如下所示。#001 Sub DelmyTools()#002 Application.CommandBars(“Worksheet menu bar“).Reset#003 End Sub代码解析：DelmyTools 过程使用 Reset 方法重置菜单栏，删除添加的菜单项及子菜单。为了在打开工作簿时自动添加菜单项，需要在工作簿的 Activate 事件中调用 myTools过程，如下面的代码所示。#001 Private Sub Wor
11、kbook_Activate()#002 Call myTools#003 End SubVBA 常用技巧代码解析 5为了在关闭工作簿时删除新添加的菜单项，还需要在工作簿的 Deactivate 事件中调用DelmyTools 过程，如下面的代码所示。#001 Private Sub Workbook_Deactivate()#002 Call DelmyTools#003 End Sub如果希望这个菜单为所有工作簿使用，那么就应该在工作簿的 Open 事件中调用myTools 过程，在 BeforeClose 事件中调用 DelmyTools 过程。运行 myTools 过程，将在 Exce
12、l 工作表菜单栏中的“帮助 ”菜单中添加一个名为“Excel Home 技术论坛”的菜单项及五个子菜单，如 图 79-1 所示。图 1-1 在“帮助”菜单中添加菜单项及子菜单技巧 2 在菜单栏指定位置添加菜单除了可以在工作表菜单中添加菜单项外，还可以在工作表菜单栏的指定位置添加菜单，如下面的代码所示。#001 Sub AddNewMenu()#002 Dim HelpMenu As CommandBarControl#003 Dim NewMenu As CommandBarPopup#004 With Application.CommandBars(“Worksheet menu bar“)
13、#005 .Reset#006 Set HelpMenu = .FindControl(ID:=.Controls(“帮助(&H)“).ID)#007 If HelpMenu Is Nothing Then#008 Set NewMenu = .Controls.Add(Type:=msoControlPopup)VBA 常用技巧代码解析 6#009 Else#010 Set NewMenu = .Controls.Add(Type:=msoControlPopup, _#011 Before:=HelpMenu.Index)#012 End If#013 With NewMenu#014 .
14、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#026 End With#027 Set Hel
15、pMenu = Nothing#028 Set NewMenu = Nothing#029 End Sub代码解析：AddNewMenu 过程使用 Add 方法在工作表“帮助”菜单前添加一个标题为“统计”的菜单和两个菜单项。第 6 行代码，使用 FindControl 方法在工作表菜单栏中查找“帮助”菜单。应用于CommandBars 对象的 FindControl 方法返回一个符合指定条件的 CommandBarControl 对象。语法如下：expression.FindControl(Type, Id, Tag, Visible, Recursive)参数 expression 是必需的
16、，返回一个 CommandBars 对象。参数 Type 是可选的，要查找控件的类型。参数 Id 是可选的，要查找控件的标识符。参数 Tag 是可选的，要查找控件的标记值。VBA 常用技巧代码解析 7参数 Visible 是可选，如果该值为 True，那么只查找屏幕上显示的命令栏控件。默认值为 False。参数 Recursive 是可选的，如果该值为 True，那么将在命令栏及其全部弹出式子工具栏中查找。此参数仅应用于 CommandBar 对象。默认值为 False。如果没有控件符合搜索条件，那么 FindControl 方法返回 Nothing。第 7 行到第 12 行代码，如果工作表菜
17、单栏中存在“帮助 ”菜单，将“统计”菜单添加到“帮助”菜单之前，否则添加到工作表菜单栏末尾。第 12 行到第 25 行代码，在“统计”菜单中添加两个子菜单并设置其各种属性。运行 AddNewMenu 过程，将在工作表菜单栏的“帮助”菜单之前添加一个“统计”菜单，如 图 80-1 所示。图 2-1 在工作表菜单栏中添加菜单技巧 3 屏蔽和删除工作表菜单如果不希望用户使用工作表菜单栏的部分功能，可以把菜单或菜单项屏蔽或删除，如下面的代码所示。#001 Sub Shibar()#002 With Application.CommandBars(“Worksheet menu bar“)#003 .R
18、eset#004 .Controls(“工具(&T)“).Controls(“ 宏(&M)“).Enabled = False#005 .Controls(“数据(&D)“).Delete#006 End With#007 End Sub代码解析：Shibar 过程屏蔽 “工具”菜单中的 “宏”菜单项，删除菜单栏中的“数据”菜单。第 3 行代码，使用 Reset 方法重置工作表菜单栏。VBA 常用技巧代码解析 8第 4 行代码，将“宏”菜单项的 Enabled 属性设置为 False，使之无效。Enabled 属性决定命令栏或命令栏控件是否激活，如果将该属性设置为 False，那么该菜单项将无
19、效。第 5 行代码，使用 Delete 方法将“数据”菜单从工作表菜单栏中删除。Delete 方法应用于命令栏或命令栏控件时，从集合中删除指定对象，语法如下：expression.Delete(Temporary)参数 expression 是必需的，返回命令栏或命令栏控件对象之一。参数 Temporary 是可选的，设置为 True 将从当前会话中删除控件，应用程序在下次会话时将再次显示控件。运行 Shibar 过程，将屏蔽工作表“工具”菜单中的“宏”菜单项和删除工作表菜单栏中的“数据”菜单，如 图 81-1 所示。图 3-1 屏蔽和删除工作表菜单技巧 4 改变系统菜单的操作利用 VBA 甚
20、至可以改变系统菜单的默认操作，使之达到自定义菜单的效果，如下面的代码所示。#001 Dim WithEvents Saveas As CommandBarButton#002 Private Sub Workbook_Open()#003 Set Saveas = Application.CommandBars(“File“).Controls(“另存为(&A).“)#004 End Sub#005 Private Sub Saveas_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)#006 Canc
21、elDefault = True#007 MsgBox “本工作簿禁止另存!“VBA 常用技巧代码解析 9#008 End Sub代码解析：第 1 行代码，在模块级别中使用关键词 WithEvents 声明变量 Saveas 是用来响应由CommandBarButton 对象触发事件的对象变量。第 2 行到第 4 代码工作簿的 Open 事件过程，在工作簿打开时将变量 Saveas 赋值为系统菜单的“另存为”菜单。因为在声明变量 Saveas 时使用了关键词 WithEvents，不能同时使用 New 关键词隐式地创建对象，所以在使用变量 Saveas 之前，必须使用 Set 语句将变量赋值为
22、一个已有对象。第 5 行到第 8 代码变量 Saveas 的单击事件过程，改变系统菜单“另存为”的默认操作。变量 Saveas 的 Click 事件在用户单击系统菜单 “另存为 ”时发生，语法如下：Private Sub CommandBarButton_Click(ByVal Ctrl As CommandBarButton,ByVal CancelDefault As Boolean)参数 Ctrl 是必需的，指示初始化该事件的 CommandBarButton 控件。参数 CancelDefault 是必需的，Boolean 类型，如果执行了与 CommandBarButton 控件关联
23、的默认操作，该值为 False。除非其他过程或加载项取消了此操作。第 6、7 行代码，将 CancelDefault 参数设置为 True，使单击 “另存为”菜单时并不执行默认操作而只显示一个消息框。将工作簿保存、关闭后，重新打开，单击“另存为”菜单并不执行默认操作，只显示一个消息框，如 图 82-1 所示。图 4-1 改变系统菜单的默认操作技巧 5 定制自己的系统菜单使用 VBA 开发的小型应用系统完成后，Excel 原有的菜单栏完全可以舍弃不用，只使VBA 常用技巧代码解析 10用自定义的菜单栏，更加方便快捷，如下面的代码所示。#001 Sub AddNowBar()#002 Dim Ne
24、wBar As CommandBar#003 On Error Resume Next#004 With Application#005 .CommandBars(“Standard“).Visible = False #006 .CommandBars(“Formatting“).Visible = False #007 .CommandBars(“Stop Recording“).Visible = False#008 .CommandBars(“toolbar list“).Enabled = False#009 .CommandBars.DisableAskAQuestionDropd
25、own = True#010 .DisplayFormulaBar = False #011 .CommandBars(“NewBar“).Delete#012 End With#013 Set NewBar = Application.CommandBars.Add(Name:=“NewBar“, Position:=msoBarTop, MenuBar:=True, Temporary:=True)#014 With NewBar#015 .Visible = True#016 With .Controls.Add(Type:=msoControlPopup)#017 .Caption =
26、 “系统设置(&X)“#018 .BeginGroup = True#019 With .Controls.Add(Type:=msoControlButton)#020 .Caption = “保存(&S)“#021 .BeginGroup = True#022 .FaceId = 1975#023 End With#024 With .Controls.Add(Type:=msoControlButton)#025 .Caption = “备份(&B)“#026 .BeginGroup = True#027 .FaceId = 747#028 End With#029 End With#030 With .Controls.Add(Type:=msoControlPopup)
