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

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

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

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

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

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

[img][/img]

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

下面是子過程代碼：

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圖像和相相應(yīng)的整數(shù)。該代碼由JohnD.McLean編寫，代碼清單如下：

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

'相應(yīng)的最左/右列和最頂/底行中的數(shù)字相加即為該圖標(biā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)建帶有一個按鈕的臨時工具欄

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'循環(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號

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

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

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

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

先來看看一個示例，該示例演示了這項技術(shù)。

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

SubCreateMenu()

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

'注:在這個子過程中沒有錯誤解決語句.

DimMenuSheetAsWorksheet

DimMenuObjectAsCommandBarPopup

DimMenuItemAsObject

DimSubMenuItemAsCommandBarButton

DimRowAsInteger

DimMenuLevel,NextLevel,PositionOrMacro,Caption,Divider,FaceId

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

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

SetMenuSheet=ThisWorkbook.Sheets("MenuSheet")

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

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

CallDeleteMenu

'行初始值

Row=2

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

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

'這個子過程在工作簿關(guān)閉時執(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"您可以在本過程中添加相應(yīng)的操作代碼."

EndSub

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

[img][/img]

圖2：存放菜單項的表格

該表格包含5列：

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

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

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

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

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

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

[img][/img]

圖3：一個自定義菜單的例子

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

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

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

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

PrivateSubWorkbook_Open()

CallCreateMenu

EndSubPrivateSubWorkbook_BeforeClose(CancelAsBoolean)

CallDeleteMenu

EndSub

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

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

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

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

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

[img][/img]

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

——只用于一個工作簿

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

在Excel2023中打開該工作簿。

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

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

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

[img][/img]

圖5：在“Excel選項”中添加自定義菜單

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

其實(shí)，在示例工作簿中隱藏著一個工作表，該工作表上存放著菜單項名稱、所執(zhí)行的宏名及圖標(biāo)號等?？梢栽谌我还ぷ鞅順?biāo)簽中單擊右鍵，選擇“取消隱藏”命令，或在“開始”功能區(qū)中選擇“格式”下的“隱藏/取消隱藏”中相應(yīng)的命令來顯示該工作表。該工作表如圖6所示。

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

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

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

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

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