2023年在Excel中創(chuàng)建自定義菜單并為菜單項(xiàng)指定宏

在Excel2023中創(chuàng)建自定義菜單并為菜單項(xiàng)指定宏Excel2023使用了新的用戶界面，每項(xiàng)功能都在稱(chēng)作Ribbon的功能區(qū)中且它們的位置都是固定的，僅快速訪問(wèn)工具欄(QAT)與先前版本的工具欄相似，可用來(lái)添加或刪除命令。因此，在Excel2023中創(chuàng)建自定義菜單并為菜單項(xiàng)指定宏不像在Excel2023中那樣容易。本文匯總了JohnWalkenbach、JohnMcLea和RondeBruin所介紹的技術(shù)。

----技術(shù)基礎(chǔ)

——辨認(rèn)工具欄圖像

假如使用Excel97或以后的版本，您知道它使用一些圖像在它的內(nèi)置菜單和工具欄中。您可以通過(guò)設(shè)立FaceID屬性為一個(gè)特定的整數(shù)在自定義菜單和工具欄中使用這些內(nèi)置圖像。然而，問(wèn)題是如何知道圖像所相應(yīng)的整數(shù)。

下面的子過(guò)程創(chuàng)建了一個(gè)帶有開(kāi)始的250個(gè)FaceID圖像(見(jiàn)下圖1所示)的自定義工具欄。創(chuàng)建了工具欄之后，將鼠標(biāo)指針?lè)旁诎粹o上面來(lái)找到與圖像相應(yīng)的FaceID值。但單擊工具欄按鈕不會(huì)產(chǎn)生任何效果，由于子過(guò)程沒(méi)有在OnAction屬性中分派任何宏。當(dāng)然，您可以通過(guò)改變IDStart和IDStop的值來(lái)看到更多的圖像，最后一個(gè)FaceID圖像顯示數(shù)字3518(也有一些空白圖像)。

[img][/img]

圖1：創(chuàng)建一個(gè)FaceID工具欄，當(dāng)鼠標(biāo)放在某圖像上時(shí)將顯示相應(yīng)的數(shù)字

下面是子過(guò)程代碼：

SubShowFaceIDs()

DimNewToolbarAsCommandBar

DimNewButtonAsCommandBarButton

DimiAsInteger,IDStartAsInteger,IDStopAsInteger

'假如已存在FaceIds工具欄則刪除

OnErrorResumeNext

Application.CommandBars("FaceIds").Delete

OnErrorGoTo0

'添加一個(gè)空工具欄

SetNewToolbar=Application.CommandBars.Add_

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

NewToolbar.Visible=True

'可以改變下面的值來(lái)看到不同的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圖像和相相應(yīng)的整數(shù)。該代碼由JohnD.McLean編寫(xiě)，代碼清單如下：

'在工作表中顯示所有工具欄按鈕圖像/圖標(biāo)，由36行100列組成

'相應(yīng)的最左/右列和最頂/底行中的數(shù)字相加即為該圖標(biāo)號(hào)

SubDisplayButtonFacesInGrid()

ConstcbName="JDMTestToolBar"

DimcBarAsCommandBar,cButAsCommandBarControl

DimrAsLong,cAsInteger,countAsInteger

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

Workbooks.Add

'創(chuàng)建四周的數(shù)字

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'創(chuàng)建帶有一個(gè)按鈕的臨時(shí)工具欄

WithcBar

.Name=cbName

.Top=0

.Left=0

.Visible=True

EndWith

r=2:c=2:count=0'在單元格Cell(2,2)中的FaceID號(hào)為0

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

WithcBut

Do'循環(huán)所有的FaceIDs

.FaceId=count

Cells(r,c).Select'分派至按鈕然后復(fù)制到工作表

.CopyFace

Selection.PasteSpecial

Cells(1,1).Copy

c=c+1

Ifc>=102Then'更新復(fù)制的位置

c=2

r=r+1

EndIf

count=count+1

LoopWhilecount<3519'3519是最大的FaceID號(hào)

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'格式化網(wǎng)格線和背景

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

'恢復(fù)Excel設(shè)立

CommandBars(cbName).Delete

OnErrorGoTo0

Range("A1").Select

Application.ScreenUpdating=True

Application.StatusBar=""

EndSub運(yùn)營(yíng)上面的代碼后，將新建一個(gè)工作簿，并在該工作簿內(nèi)列出所有的內(nèi)置圖標(biāo)圖像，最左列、最右列、最頂部、最底部為相應(yīng)的數(shù)字，將某圖標(biāo)相應(yīng)的最左(或右)列的數(shù)字與最頂一行(或最底一行)的數(shù)字相加，即為該圖標(biāo)相應(yīng)的數(shù)字。

(注：上面的代碼運(yùn)營(yíng)較慢，需耐心等待。)

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

在Excel97至Excel2023等版本中，可以運(yùn)用“自定義”對(duì)話框來(lái)創(chuàng)建新菜單，并建立菜單項(xiàng)，但很難創(chuàng)建子菜單。因此，特定的工作簿菜單必須編寫(xiě)VBA代碼來(lái)創(chuàng)建。下面的技術(shù)介紹了使用一種相稱(chēng)簡(jiǎn)樸的方法在工作表菜單欄中創(chuàng)建自定義菜單，當(dāng)工作簿打開(kāi)時(shí)則顯示自定義的菜單，該工作簿關(guān)閉時(shí)則刪除自定義的菜單。

先來(lái)看看一個(gè)示例，該示例演示了這項(xiàng)技術(shù)。

示例文獻(xiàn)包含了所有需要?jiǎng)?chuàng)建自定義菜單的VBA代碼，在大多數(shù)情況下，不需要改變這些代碼，只需按自已的意圖簡(jiǎn)樸地自定義MenuSheet工作表即可。VBA代碼清單如下：

SubCreateMenu()

'當(dāng)工作簿打開(kāi)時(shí)本過(guò)程自動(dòng)執(zhí)行.

'注:在這個(gè)子過(guò)程中沒(méi)有錯(cuò)誤解決語(yǔ)句.

DimMenuSheetAsWorksheet

DimMenuObjectAsCommandBarPopup

DimMenuItemAsObject

DimSubMenuItemAsCommandBarButton

DimRowAsInteger

DimMenuLevel,NextLevel,PositionOrMacro,Caption,Divider,FaceId

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

'獲取菜單數(shù)據(jù)的位置

SetMenuSheet=ThisWorkbook.Sheets("MenuSheet")

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

'保證菜單不反復(fù)

CallDeleteMenu

'行初始值

Row=2

'使用MenuSheet工作表中的數(shù)據(jù)添加菜單,菜單項(xiàng)和子菜單項(xiàng)

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'代表菜單

'添加頂級(jí)菜單到工作表菜單欄中

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

Before:=PositionOrMacro,_

Temporary:=True)

MenuObject.Caption=Caption

Case2'代表菜單項(xiàng)

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'代表子菜單項(xiàng)

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()

'這個(gè)子過(guò)程在工作簿關(guān)閉時(shí)執(zhí)行

'刪除自定義菜單

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"您可以在本過(guò)程中添加相應(yīng)的操作代碼."

EndSub

換句話說(shuō)，該技術(shù)使用了一個(gè)存放在MenuSheet工作表中的表格(如下圖2所示)，只需按自已的需要簡(jiǎn)樸地修改表中的數(shù)據(jù)，就可創(chuàng)建自已的菜單。

[img][/img]

圖2：存放菜單項(xiàng)的表格

該表格包含5列：

(1)級(jí)別：指定的菜單項(xiàng)的級(jí)別，有效值是1、2、3。第1級(jí)別是菜單，第2級(jí)別是菜單項(xiàng)，第3級(jí)別是子菜單項(xiàng)。正常情況下，有一個(gè)第1級(jí)別的菜單，下面包具有第2級(jí)別的菜單項(xiàng)。一個(gè)第2級(jí)別的菜單項(xiàng)也許包含或不包具有第3級(jí)別的菜單項(xiàng)(子菜單項(xiàng))。

(2)標(biāo)題：顯示在菜單、菜單項(xiàng)和子菜單項(xiàng)中的文字。使用連接符(&)指定一個(gè)帶下劃線的字符。

(3)位置/宏：對(duì)于第1級(jí)菜單，應(yīng)當(dāng)是一個(gè)整數(shù)，代表菜單在菜單欄中的位置。對(duì)于第2級(jí)或第3級(jí)菜單項(xiàng)，應(yīng)當(dāng)是一個(gè)宏，當(dāng)該菜單項(xiàng)被選擇時(shí)執(zhí)行相應(yīng)的宏。假如第2級(jí)菜單項(xiàng)有一個(gè)或多個(gè)第3級(jí)菜單項(xiàng)，第2級(jí)菜單項(xiàng)也許沒(méi)有一個(gè)宏與它相關(guān)聯(lián)。

(4)分隔線：假如設(shè)立為真，將在菜單項(xiàng)或子菜單項(xiàng)前放置一個(gè)分隔線。

(5)FaceID(圖標(biāo)號(hào))：可選的。一個(gè)代碼數(shù)字，代表顯示在菜單項(xiàng)前內(nèi)置的圖形圖像。獲取代碼數(shù)字可見(jiàn)上文所介紹的辨認(rèn)工具欄圖像的內(nèi)容。

下圖3顯示了使用上面的表格所創(chuàng)建的自定義菜單。

[img][/img]

圖3：一個(gè)自定義菜單的例子

要在工作簿或者加載宏中使用這項(xiàng)技術(shù)，可以按照下面的環(huán)節(jié)進(jìn)行：

(1)打開(kāi)前面下載的工作簿文獻(xiàn)。該工作簿包具有VBA代碼和一個(gè)名為MenuSheet的工作表。

(2)將該工作簿中的所有代碼復(fù)制到自已的VBA工程的模塊中。

(3)將下面的子過(guò)程添加到ThisWorkbook對(duì)象模塊中：

PrivateSubWorkbook_Open()

CallCreateMenu

EndSubPrivateSubWorkbook_BeforeClose(CancelAsBoolean)

CallDeleteMenu

EndSub

(4)當(dāng)工作簿打開(kāi)時(shí)，執(zhí)行Workbook_Open子過(guò)程，當(dāng)工作簿關(guān)閉時(shí)，執(zhí)行Workbook_BeforeClose子過(guò)程。

(5)插入一個(gè)新工作表并命名為MenuSheet。然后直接復(fù)制menumakr.xls文獻(xiàn)中的表格到MenuSheet工作表中。

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

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

下面的內(nèi)容應(yīng)用了前面所講的技術(shù)在Excel2023中創(chuàng)建自定義菜單，并為菜單項(xiàng)指定相應(yīng)的宏，如圖4所示。

[img][/img]

圖4：在Excel2023中自定義菜單示例

——只用于一個(gè)工作簿

可以按下面的環(huán)節(jié)在特定的Excel2023工作簿中創(chuàng)建自定義菜單：

在Excel2023中打開(kāi)該工作簿。

在快速訪問(wèn)工具欄(QAT)中單擊右鍵，選擇“自定義快速訪問(wèn)工具欄”，彈出“Excel選項(xiàng)”對(duì)話框。在對(duì)話框中的“從下列位置選擇命令”下拉列表框中選擇“宏”，然后在右側(cè)的“自定義快速訪問(wèn)工具欄”下拉列表框中選擇“用于MyWorkbook.xlsm”。

然后，在左側(cè)的列表框中選擇“WBDisplayPopUp”，單擊“添加”按鈕，再單擊“擬定”按鈕。

假如想修改所要顯示的圖標(biāo)，可單擊下方的“修改”按鈕。

[img][/img]

圖5：在“Excel選項(xiàng)”中添加自定義菜單

(4)此時(shí)，快速訪問(wèn)工具欄中新增了一個(gè)圖標(biāo)，點(diǎn)擊該圖標(biāo)將彈出自定義菜單。能使用Ctrl+M組合鍵快速打開(kāi)菜單，也能使用“宏”對(duì)話框(按Alt+F8鍵)修改快捷鍵。

其實(shí)，在示例工作簿中隱藏著一個(gè)工作表，該工作表上存放著菜單項(xiàng)名稱(chēng)、所執(zhí)行的宏名及圖標(biāo)號(hào)等。可以在任一工作表標(biāo)簽中單擊右鍵，選擇“取消隱藏”命令，或在“開(kāi)始”功能區(qū)中選擇“格式”下的“隱藏/取消隱藏”中相應(yīng)的命令來(lái)顯示該工作表。該工作表如圖6所示。

圖6：存放菜單項(xiàng)名、宏名及圖標(biāo)號(hào)的MenuSheet工作表

與前面所講述的內(nèi)容同樣，該工作表中包含5列，分別為：

(1)級(jí)別：指定的菜單項(xiàng)的級(jí)別，有效值是2和3。第2級(jí)別是菜單項(xiàng)，第3級(jí)別是子菜單項(xiàng)。

(2)標(biāo)題：顯示在菜單、菜單項(xiàng)和子菜單項(xiàng)中的文字。使用連接符(&)指定一個(gè)帶下劃線的字符。

(3)宏：對(duì)于第2級(jí)或第3級(jí)菜單項(xiàng)，應(yīng)當(dāng)是一個(gè)宏，當(dāng)該菜單項(xiàng)被選擇時(shí)執(zhí)行相