2023年在Excel中创建自定义菜单并为菜单项指定宏

在Excel2023中创建自定义菜单并为菜单项指定宏Excel2023使用了新的用户界面，每项功能都在称作Ribbon的功能区中且它们的位置都是固定的，仅快速访问工具栏(QAT)与先前版本的工具栏相似，可用来添加或删除命令。因此，在Excel2023中创建自定义菜单并为菜单项指定宏不像在Excel2023中那样容易。本文汇总了JohnWalkenbach、JohnMcLea和RondeBruin所介绍的技术。

----技术基础

——辨认工具栏图像

假如使用Excel97或以后的版本，您知道它使用一些图像在它的内置菜单和工具栏中。您可以通过设立FaceID属性为一个特定的整数在自定义菜单和工具栏中使用这些内置图像。然而，问题是如何知道图像所相应的整数。

下面的子过程创建了一个带有开始的250个FaceID图像(见下图1所示)的自定义工具栏。创建了工具栏之后，将鼠标指针放在按钮上面来找到与图像相应的FaceID值。但单击工具栏按钮不会产生任何效果，由于子过程没有在OnAction属性中分派任何宏。当然，您可以通过改变IDStart和IDStop的值来看到更多的图像，最后一个FaceID图像显示数字3518(也有一些空白图像)。

[img][/img]

图1：创建一个FaceID工具栏，当鼠标放在某图像上时将显示相应的数字

下面是子过程代码：

SubShowFaceIDs()

DimNewToolbarAsCommandBar

DimNewButtonAsCommandBarButton

DimiAsInteger,IDStartAsInteger,IDStopAsInteger

'假如已存在FaceIds工具栏则删除

OnErrorResumeNext

Application.CommandBars("FaceIds").Delete

OnErrorGoTo0

'添加一个空工具栏

SetNewToolbar=Application.CommandBars.Add_

(Name:="FaceIds",temporary:=True)

NewToolbar.Visible=True

'可以改变下面的值来看到不同的FaceIDs

IDStart=1

IDStop=250

Fori=IDStartToIDStop

SetNewButton=NewToolbar.Controls.Add_

(Type:=msoControlButton,ID:=2950)

NewButton.FaceId=i

NewButton.Caption="FaceID="&i

Nexti

NewToolbar.Width=600

EndSub

此外，也可以使用VBA代码在工作表中列出所有的FaceID图像和相相应的整数。该代码由JohnD.McLean编写，代码清单如下：

'在工作表中显示所有工具栏按钮图像/图标，由36行100列组成

'相应的最左/右列和最顶/底行中的数字相加即为该图标号

SubDisplayButtonFacesInGrid()

ConstcbName="JDMTestToolBar"

DimcBarAsCommandBar,cButAsCommandBarControl

DimrAsLong,cAsInteger,countAsInteger

Application.StatusBar="CreatingButtonFaceIDs......."

Workbooks.Add

'创建四周的数字

Forr=0To35

Cells(r+2,1).Value=100*r:Cells(r+2,102).Value=100*r

Nextr

Forc=0To99

Cells(1,c+2).Value=c:Cells(38,c+2).Value=c

Nextc

Range("A1:A38").Select

WithSelection

.Font.Bold=True

.HorizontalAlignment=xlCenter

.VerticalAlignment=xlCenter

EndWith

Range("CX1:CX38").Select

WithSelection

.Font.Bold=True

.HorizontalAlignment=xlCenter

.VerticalAlignment=xlCenter

EndWith

Range("B1:CW1").Select

WithSelection

.Font.Bold=True

.HorizontalAlignment=xlCenter

.VerticalAlignment=xlCenter

EndWith

Range("B38:CW38").Select

WithSelection

.Font.Bold=True

.HorizontalAlignment=xlCenter

.VerticalAlignment=xlCenter

EndWith

Range("E5").Select

Selection.Value="Pleasewait.............."

WithSelection.Font

.Name="Arial"

.Size=24

.Bold=True

.ColorIndex=3

EndWith

Range("E5:J10").Select

WithSelection

.HorizontalAlignment=xlCenter

.VerticalAlignment=xlCenter

.MergeCells=True

EndWith

Application.ScreenUpdating=False

WithSelection

.ClearContents

.UnMerge

EndWith

OnErrorResumeNext

CommandBars(cbName).Delete

OnErrorGoTo0

SetcBar=CommandBars.Add'创建带有一个按钮的临时工具栏

WithcBar

.Name=cbName

.Top=0

.Left=0

.Visible=True

EndWith

r=2:c=2:count=0'在单元格Cell(2,2)中的FaceID号为0

SetcBut=CommandBars(cbName).Controls.Add(Type:=msoControlButton)

WithcBut

Do'循环所有的FaceIDs

.FaceId=count

Cells(r,c).Select'分派至按钮然后复制到工作表

.CopyFace

Selection.PasteSpecial

Cells(1,1).Copy

c=c+1

Ifc>=102Then'更新复制的位置

c=2

r=r+1

EndIf

count=count+1

LoopWhilecount<3519'3519是最大的FaceID号

EndWith

Rows("1:38").RowHeight=24.6'增大单元格尺寸

Columns("A:CX").ColumnWidth=5.56

WithActiveSheet.DrawingObjects'增大按钮尺寸

.ShapeRange.ScaleWidth2#,msoFalse,msoScaleFromTopLeft

.ShapeRange.ScaleHeight2#,msoFalse,msoScaleFromTopLeft

.ShapeRange.IncrementLeft8.4

.ShapeRange.IncrementTop3#

EndWith

Range(Cells(2,2),Cells(37,101)).Select'格式化网格线和背景

WithSelection

With.Interior

.ColorIndex=15

.Pattern=xlSolid

EndWith

With.Borders(xlInsideVertical)

.LineStyle=xlContinuous

.Weight=xlThin

.ColorIndex=2

EndWith

With.Borders(xlInsideHorizontal)

.LineStyle=xlContinuous

.Weight=xlThin

.ColorIndex=2

EndWith

EndWith

'恢复Excel设立

CommandBars(cbName).Delete

OnErrorGoTo0

Range("A1").Select

Application.ScreenUpdating=True

Application.StatusBar=""

EndSub运营上面的代码后，将新建一个工作簿，并在该工作簿内列出所有的内置图标图像，最左列、最右列、最顶部、最底部为相应的数字，将某图标相应的最左(或右)列的数字与最顶一行(或最底一行)的数字相加，即为该图标相应的数字。

(注：上面的代码运营较慢，需耐心等待。)

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

在Excel97至Excel2023等版本中，可以运用“自定义”对话框来创建新菜单，并建立菜单项，但很难创建子菜单。因此，特定的工作簿菜单必须编写VBA代码来创建。下面的技术介绍了使用一种相称简朴的方法在工作表菜单栏中创建自定义菜单，当工作簿打开时则显示自定义的菜单，该工作簿关闭时则删除自定义的菜单。

先来看看一个示例，该示例演示了这项技术。

示例文献包含了所有需要创建自定义菜单的VBA代码，在大多数情况下，不需要改变这些代码，只需按自已的意图简朴地自定义MenuSheet工作表即可。VBA代码清单如下：

SubCreateMenu()

'当工作簿打开时本过程自动执行.

'注:在这个子过程中没有错误解决语句.

DimMenuSheetAsWorksheet

DimMenuObjectAsCommandBarPopup

DimMenuItemAsObject

DimSubMenuItemAsCommandBarButton

DimRowAsInteger

DimMenuLevel,NextLevel,PositionOrMacro,Caption,Divider,FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''

'获取菜单数据的位置

SetMenuSheet=ThisWorkbook.Sheets("MenuSheet")

''''''''''''''''''''''''''''''''''''''''''''''''''''

'保证菜单不反复

CallDeleteMenu

'行初始值

Row=2

'使用MenuSheet工作表中的数据添加菜单,菜单项和子菜单项

DoUntilIsEmpty(MenuSheet.Cells(Row,1))

WithMenuSheet

MenuLevel=.Cells(Row,1)

Caption=.Cells(Row,2)

PositionOrMacro=.Cells(Row,3)

Divider=.Cells(Row,4)

FaceId=.Cells(Row,5)

NextLevel=.Cells(Row+1,1)

EndWith

SelectCaseMenuLevel

Case1'代表菜单

'添加顶级菜单到工作表菜单栏中

SetMenuObject=Application.CommandBars(1)._Controls.Add(Type:=msoControlPopup,_

Before:=PositionOrMacro,_

Temporary:=True)

MenuObject.Caption=Caption

Case2'代表菜单项

IfNextLevel=3Then

SetMenuItem=MenuObject.Controls.Add(Type:=msoControlPopup)

Else

SetMenuItem=MenuObject.Controls.Add(Type:=msoControlButton)

MenuItem.OnAction=PositionOrMacro

EndIf

MenuItem.Caption=Caption

IfFaceId<>""ThenMenuItem.FaceId=FaceId

IfDividerThenMenuItem.BeginGroup=True

Case3'代表子菜单项

SetSubMenuItem=MenuItem.Controls.Add(Type:=msoControlButton)

SubMenuItem.Caption=Caption

SubMenuItem.OnAction=PositionOrMacro

IfFaceId<>""ThenSubMenuItem.FaceId=FaceId

IfDividerThenSubMenuItem.BeginGroup=True

EndSelect

Row=Row+1

Loop

EndSub

SubDeleteMenu()

'这个子过程在工作簿关闭时执行

'删除自定义菜单

DimMenuSheetAsWorksheet

DimRowAsInteger

DimCaptionAsString

OnErrorResumeNext

SetMenuSheet=ThisWorkbook.Sheets("MenuSheet")

Row=2

DoUntilIsEmpty(MenuSheet.Cells(Row,1))

IfMenuSheet.Cells(Row,1)=1Then

Caption=MenuSheet.Cells(Row,2)

Application.CommandBars(1).Controls(Caption).Delete

EndIf

Row=Row+1

Loop

OnErrorGoTo0

EndSub

SubDummyMacro()

MsgBox"您可以在本过程中添加相应的操作代码."

EndSub

换句话说，该技术使用了一个存放在MenuSheet工作表中的表格(如下图2所示)，只需按自已的需要简朴地修改表中的数据，就可创建自已的菜单。

[img][/img]

图2：存放菜单项的表格

该表格包含5列：

(1)级别：指定的菜单项的级别，有效值是1、2、3。第1级别是菜单，第2级别是菜单项，第3级别是子菜单项。正常情况下，有一个第1级别的菜单，下面包具有第2级别的菜单项。一个第2级别的菜单项也许包含或不包具有第3级别的菜单项(子菜单项)。

(2)标题：显示在菜单、菜单项和子菜单项中的文字。使用连接符(&)指定一个带下划线的字符。

(3)位置/宏：对于第1级菜单，应当是一个整数，代表菜单在菜单栏中的位置。对于第2级或第3级菜单项，应当是一个宏，当该菜单项被选择时执行相应的宏。假如第2级菜单项有一个或多个第3级菜单项，第2级菜单项也许没有一个宏与它相关联。

(4)分隔线：假如设立为真，将在菜单项或子菜单项前放置一个分隔线。

(5)FaceID(图标号)：可选的。一个代码数字，代表显示在菜单项前内置的图形图像。获取代码数字可见上文所介绍的辨认工具栏图像的内容。

下图3显示了使用上面的表格所创建的自定义菜单。

[img][/img]

图3：一个自定义菜单的例子

要在工作簿或者加载宏中使用这项技术，可以按照下面的环节进行：

(1)打开前面下载的工作簿文献。该工作簿包具有VBA代码和一个名为MenuSheet的工作表。

(2)将该工作簿中的所有代码复制到自已的VBA工程的模块中。

(3)将下面的子过程添加到ThisWorkbook对象模块中：

PrivateSubWorkbook_Open()

CallCreateMenu

EndSubPrivateSubWorkbook_BeforeClose(CancelAsBoolean)

CallDeleteMenu

EndSub

(4)当工作簿打开时，执行Workbook_Open子过程，当工作簿关闭时，执行Workbook_BeforeClose子过程。

(5)插入一个新工作表并命名为MenuSheet。然后直接复制menumakr.xls文献中的表格到MenuSheet工作表中。

(6)按自已的需要修改MenuSheet工作表中的表格。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

下面的内容应用了前面所讲的技术在Excel2023中创建自定义菜单，并为菜单项指定相应的宏，如图4所示。

[img][/img]

图4：在Excel2023中自定义菜单示例

——只用于一个工作簿

可以按下面的环节在特定的Excel2023工作簿中创建自定义菜单：

在Excel2023中打开该工作簿。

在快速访问工具栏(QAT)中单击右键，选择“自定义快速访问工具栏”，弹出“Excel选项”对话框。在对话框中的“从下列位置选择命令”下拉列表框中选择“宏”，然后在右侧的“自定义快速访问工具栏”下拉列表框中选择“用于MyWorkbook.xlsm”。

然后，在左侧的列表框中选择“WBDisplayPopUp”，单击“添加”按钮，再单击“拟定”按钮。

假如想修改所要显示的图标，可单击下方的“修改”按钮。

[img][/img]

图5：在“Excel选项”中添加自定义菜单

(4)此时，快速访问工具栏中新增了一个图标，点击该图标将弹出自定义菜单。能使用Ctrl+M组合键快速打开菜单，也能使用“宏”对话框(按Alt+F8键)修改快捷键。

其实，在示例工作簿中隐藏着一个工作表，该工作表上存放着菜单项名称、所执行的宏名及图标号等。可以在任一工作表标签中单击右键，选择“取消隐藏”命令，或在“开始”功能区中选择“格式”下的“隐藏/取消隐藏”中相应的命令来显示该工作表。该工作表如图6所示。

图6：存放菜单项名、宏名及图标号的MenuSheet工作表

与前面所讲述的内容同样，该工作表中包含5列，分别为：

(1)级别：指定的菜单项的级别，有效值是2和3。第2级别是菜单项，第3级别是子菜单项。

(2)标题：显示在菜单、菜单项和子菜单项中的文字。使用连接符(&)指定一个带下划线的字符。

(3)宏：对于第2级或第3级菜单项，应当是一个宏，当该菜单项被选择时执行相