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

VBA常用技巧代码解析

DelmyTools过程，如下面的代码所示。

#001 Private Sub Workbook_Deactivate() #002 Call DelmyTools #003 End Sub

如果希望这个菜单为所有工作簿使用，那么就应该在工作簿的Open事件中调用myTools过程，在BeforeClose事件中调用DelmyTools过程。

运行myTools过程，将在Excel工作表菜单栏中的“帮助”菜单中添加一个名为“Excel Home 技术论坛”的菜单项及五个子菜单，如图 1-1所示。

图 1-1 在“帮助”菜单中添加菜单项及子菜单

技巧2 在菜单栏指定位置添加菜单

除了可以在工作表菜单中添加菜单项外，还可以在工作表菜单栏的指定位置添加菜单，如下面的代码所示。

#001 Sub AddNewMenu()

#002 Dim HelpMenu As CommandBarControl #003 Dim NewMenu As CommandBarPopup

#004 With Application.CommandBars(\#005 .Reset

#006 Set HelpMenu = .FindControl(ID:=.Controls(\帮助(&H)\#007 If HelpMenu Is Nothing Then

#008 Set NewMenu = .Controls.Add(Type:=msoControlPopup) #009 Else

5

VBA常用技巧代码解析

#010 Set NewMenu = .Controls.Add(Type:=msoControlPopup, _ #011 Before:=HelpMenu.Index) #012 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 #026 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)

参数expression是必需的，返回一个CommandBars对象。 参数Type是可选的，要查找控件的类型。 参数Id是可选的，要查找控件的标识符。 参数Tag是可选的，要查找控件的标记值。

参数Visible是可选，如果该值为True，那么只查找屏幕上显示的命令栏控件。默认值

6

VBA常用技巧代码解析

为False。

参数Recursive是可选的，如果该值为True，那么将在命令栏及其全部弹出式子工具栏中查找。此参数仅应用于CommandBar对象。默认值为False。

如果没有控件符合搜索条件，那么FindControl方法返回Nothing。

第7行到第12行代码，如果工作表菜单栏中存在“帮助”菜单，将“统计”菜单添加到“帮助”菜单之前，否则添加到工作表菜单栏末尾。

第12行到第25行代码，在“统计”菜单中添加两个子菜单并设置其各种属性。 运行AddNewMenu过程，将在工作表菜单栏的“帮助”菜单之前添加一个“统计”菜单，如图 2-1所示。

图 2-1 在工作表菜单栏中添加菜单

技巧3 屏蔽和删除工作表菜单

如果不希望用户使用工作表菜单栏的部分功能，可以把菜单或菜单项屏蔽或删除，如下面的代码所示。

#001 Sub Shibar()

#002 With Application.CommandBars(\#003 .Reset

#004 .Controls(\工具(&T)\宏(&M)\#005 .Controls(\数据(&D)\#006 End With #007 End Sub

代码解析：

Shibar过程屏蔽 “工具”菜单中的“宏”菜单项，删除菜单栏中的“数据”菜单。 第3行代码，使用Reset方法重置工作表菜单栏。

第4行代码，将“宏”菜单项的Enabled属性设置为False，使之无效。

7

VBA常用技巧代码解析

Enabled属性决定命令栏或命令栏控件是否激活，如果将该属性设置为 False，那么该菜单项将无效。

第5行代码，使用Delete方法将“数据”菜单从工作表菜单栏中删除。 Delete方法应用于命令栏或命令栏控件时，从集合中删除指定对象，语法如下：

expression.Delete(Temporary)

参数expression是必需的，返回命令栏或命令栏控件对象之一。

参数Temporary是可选的，设置为True将从当前会话中删除控件，应用程序在下次会话时将再次显示控件。

运行Shibar过程，将屏蔽工作表“工具”菜单中的“宏”菜单项和删除工作表菜单栏中的“数据”菜单，如图 3-1所示。

图 3-1 屏蔽和删除工作表菜单

技巧4 改变系统菜单的操作

利用VBA甚至可以改变系统菜单的默认操作，使之达到自定义菜单的效果，如下面的代码所示。

#001 Dim WithEvents Saveas As CommandBarButton #002 Private Sub Workbook_Open()

#003 Set Saveas = Application.CommandBars(\另存为(&A)...\

#004 End Sub

#005 Private Sub Saveas_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)

#006 CancelDefault = True #007 MsgBox \本工作簿禁止另存!\#008 End Sub

8