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

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移除工作表的最小最大化和关闭按钮.......................................................28技巧15在工具栏上添加下拉列表框.......................................................................29技巧16屏蔽工作表的复制功能...............................................................................31技巧17禁用工具栏的自定义...................................................................................32技巧18屏蔽所有的命令栏.......................................................................................35技巧19恢复Excel的命令栏....................................................................................36VBA常用技巧代码解析2第7章菜单和工具栏技巧1在菜单中添加菜单项在Excel工作表的菜单中可以添加新的菜单项和子菜单，如下面的代码所示。#001SubmyTools()#002DimmyToolsAsCommandBarPopup#003DimmyCapAsVariant#004DimmyidAsVariant#005DimiAsByte#006myCap=Array(基础应用,VBA程序开发,函数与公式,图表与图形,数据透视表)#007myid=Array(281,283,285,287,292)#008WithApplication.CommandBars(Worksheetmenubar)#009.Reset#010SetmyTools=.Controls(帮助(&H)).Controls.Add(Type:=msoControlPopup,Before:=1)#011WithmyTools#012.Caption=ExcelHome技术论坛#013.BeginGroup=True#014Fori=1To5#015With.Controls.Add(Type:=msoControlButton)#016.Caption=myCap(i-1)#017.FaceId=myid(i-1)#018.OnAction=myC#019EndWith#020NextVBA常用技巧代码解析3#021EndWith#022EndWith#023SetmyTools=Nothing#024EndSub代码解析：myTools过程使用Add方法在Excel工作表菜单栏中的“帮助”菜单中添加一个标题为“ExcelHome技术论坛”的菜单项和5个子菜单。第2行到第5行代码声明变量类型。第6、7行代码使用Array函数创建两个数组用于保存子菜单的名称和图标ID。第9行代码，在添加菜单项前先使用Reset方法重置菜单栏以免重复添加菜单项。Reset方法重置一个内置控件，恢复该控件原来对应的动作，并将各属性恢复成初始状态，语法如下：expression.Reset参数expression是必需的，返回一个命令栏或命令栏控件对象。第10行代码，使用Add方法在Excel工作表菜单栏中的“帮助”菜单中添加菜单项。Add方法应用于CommandBarControls对象时，新建一个CommandBarControl对象并添加到指定命令栏上的控件集合，语法如下：expression.Add(Type,Id,Parameter,Before,Temporary)参数expression是必需的，返回一个CommandBarControls对象，代表命令栏中的所有控件。参数Type是可选的，添加到指定命令栏的控件类型，可以为表格1-1所列的MsoControlType常数之一。常数值控件类型msoControlButton1命令按钮msoControlEdit2文本框msoControlDropdown3下拉列表控制框msoControlComboBox4下拉组合控制框msoControlPopup10弹出式控件表格1-1MsoControlType常数因为在本例中将添加的是带有子菜单的菜单项，所以将参数Type设置为弹出式控件。参数Id是可选的，标识整数。如果将该参数设置为1或者忽略，将在命令栏中添加一个空的指定类型的自定义控件。参数Parameter是可选的，对于内置控件，该参数用于容器应用程序运行命令。对于VBA常用技巧代码解析4自定义控件，可以使用该参数向VisualBasic过程传递信息，或用其存储控件信息。参数Before是可选的，表示新控件在命令栏上位置的数字。新控件将插入到该位置控件之前。如果忽略该参数，控件将添加到指定命令栏的末端。本例中将Before参数设置为1，菜单项添加到“帮助”菜单的顶端。参数Temporary是可选的。设置为True将使添加的菜单项为临时的，在关闭应用程序时删除。默认值为False。第12行代码，设定新添加菜单项的Caption属性为“ExcelHome技术论坛”。Caption属性返回或设置命令栏控件的标题。第13行代码，设置新添加菜单项的BeginGroup属性为True，分组显示。第14行到第19行代码，在“ExcelHome技术论坛”菜单项上添加五个子菜单并设置其Caption属性、FaceId属性和OnAction属性。FaceId属性设置出现在菜单标题左侧的图标，以数字表示，一个数字代表一个内置的图标。OnAction属性设置一个VBA的过程名，该过程在用户单击子菜单时运行，本例中设置为下面的过程。#001PublicSubmyC()#002MsgBox您选择了：&Application.CommandBars.ActionControl.Caption#003EndSub代码解析：myC过程是单击新添加子菜单所运行过程，为了演示方便在这里只使用MsgBox函数显示所其Caption属性。删除新添加的菜单项及子菜单的代码如下所示。#001SubDelmyTools()#002Application.CommandBars(Worksheetmenubar).Reset#003EndSub代码解析：DelmyTools过程使用Reset方法重置菜单栏，删除添加的菜单项及子菜单。为了在打开工作簿时自动添加菜单项，需要在工作簿的Activate事件中调用myTools过程，如下面的代码所示。#001PrivateSubWorkbook_Activate()#002CallmyTools#003EndSub为了在关闭工作簿时删除新添加的菜单项，还需要在工作簿的Deactivate事件中调用VBA常用技巧代码解析5DelmyTools过程，如下面的代码所示。#001PrivateSubWorkbook_Deactivate()#002CallDelmyTools#003EndSub如果希望这个菜单为所有工作簿使用，那么就应该在工作簿的Open事件中调用myTools过程，在BeforeClose事件中调用DelmyTools过程。运行myTools过程，将在Excel工作表菜单栏中的“帮助”菜单中添加一个名为“ExcelHome技术论坛”的菜单项及五个子菜单，如图1-1所示。图1-1在“帮助”菜单中添加菜单项及子菜单技巧2在菜单栏指定位置添加菜单除了可以在工作表菜单中添加菜单项外，还可以在工作表菜单栏的指定位置添加菜单，如下面的代码所示。#001SubAddNewMenu()#002DimHelpMenuAsCommandBarControl#003DimNewMenuAsCommandBarPopup#004WithApplication.CommandBars(Worksheetmenubar)#005.Reset#006SetHelpMenu=.FindControl(ID:=.