如何從Excel中的右鍵菜單運(yùn)行多個宏

如何從Excel中的右鍵菜單運(yùn)行多個宏？

如果您的工作簿中有多個vba宏，則應(yīng)打開VBA窗口，然后在需要運(yùn)行代碼時選擇該宏。在本文中，我想談?wù)勅绾螐挠益I單擊菜單運(yùn)行宏以使您的工作更高效，如左圖所示。

使用VBA代碼從右鍵菜單運(yùn)行多個宏

要從右鍵單擊菜單中運(yùn)行工作簿中的宏代碼，以下步驟可能會為您提供幫助：

1。向下鉆 Alt + F11鍵 鍵打開 Microsoft Visual Basic應(yīng)用程序 窗口。

2。然后，雙擊 的ThisWorkbook 在左邊 項(xiàng)目 窗格，然后將下面的VBA代碼復(fù)制并粘貼到空白模塊中。

Private Sub Workbook_Open()Run "LoadMacro"End SubPrivate Sub Workbook_Activate()Run "LoadMacro"End SubPrivate Sub Workbook_Deactivate()Run "ClearMacro"End SubPrivate Sub Workbook_BeforeClose(Cancel As Boolean)Run "ClearMacro"ThisWorkbook.SaveEnd SubCopy

3。仍在 Microsoft Visual Basic應(yīng)用程序 窗口，請點(diǎn)擊插頁 > 模塊，然后將以下代碼粘貼到該模塊中。

Private Sub LoadMacro()Dim xArrMenu As VariantDim xStrLine, xSreBtnName As StringDim xObjCBCF, xObjCntrAll As CommandBarControlDim xObjCBCs As CommandBarsDim xObjCBBtn As CommandBarButtonDim xIntLine, xFNum As IntegerDim xObjComponent As ObjectRun "ClearMacro"Set xObjCBCF = Application.CommandBars("Cell").Controls.Add(msoControlPopup, before:=1)xObjCBCF.Caption = " Run Macro "xObjCBCF.BeginGroup = FalseFor Each xObjComponent In ActiveWorkbook.VBProject.VBComponentsIf xObjComponent.Type = 1 ThenFor xIntLine = 1 To xObjComponent.CodeModule.CountOfLinesxStrLine = xObjComponent.CodeModule.Lines(xIntLine, 1)xStrLine = Trim(xStrLine)If (InStr(xStrLine, "()") > 0) And (Left(xStrLine, 11) = "Private Sub" Or Left(xStrLine, 3) = "Sub") ThenxSreBtnName = ""If "Private Sub" = Left(xStrLine, 11) ThenxSreBtnName = Trim(Mid(xStrLine, 12, InStr(xStrLine, "()") - 12))ElseIf "Sub" = Left(xStrLine, 3) ThenxSreBtnName = Trim(Mid(xStrLine, 4, InStr(xStrLine, "()") - 4))End IfIf xSreBtnName <> "" And xSreBtnName <> "RightClickReset" And xSreBtnName <> "LoadMacro" And xSreBtnName <> "ActionMacro" ThenSet xObjCBBtn = xObjCBCF.Controls.AddWith xObjCBBtn.FaceId = 186.Style = msoButtonIconAndCaption.Caption = xSreBtnName.onAction = "ActionMacro"End WithEnd IfEnd IfNext xIntLineEnd IfNext xObjComponentEnd SubPrivate Sub ClearMacro()On Error Resume NextCommandBars("Cell").Controls(" Run Macro ").DeleteErr.ClearCommandBars("Cell").ResetEnd SubPrivate Sub ActionMacro()On Error GoTo Err1With ApplicationRun .CommandBars("Cell").Controls(1).Controls(.Caller(1)).CaptionEnd WithExit SubErr1:MsgBox "Invalid"End SubCopy

4。粘貼代碼后，然后單擊工具 > 參考資料和一個 參考文獻(xiàn)-VBAProject 顯示對話框，然后檢查 Microsoft Visual Basic應(yīng)用程序可擴(kuò)展性5.3 在選項(xiàng) 可用參考 列表框，請參見屏幕截圖：

5。然后點(diǎn)擊 OK 要退出對話框，現(xiàn)在，您應(yīng)該將此工作簿另存為 Excel啟用宏的工作簿 格式，請參見屏幕截圖：

6。最后，請重新啟動工作簿以使代碼生效，現(xiàn)在，當(dāng)您右鍵單擊單元格時， 運(yùn)行宏 選項(xiàng)插入到右鍵單擊菜單中，并且工作簿中的所有宏都在子菜單中列出，如以下屏幕截圖所示：

7。然后，您可以通過單擊運(yùn)行代碼。