如何在Excel的右键菜单中运行多个宏？

如果您的工作簿中有多个VBA宏，当您需要运行代码时，应该打开VBA窗口并选择相应的宏。在本文中，我想谈谈如何通过右键菜单运行宏，以提高您的工作效率，如左图所示。

使用VBA代码从右键菜单运行多个宏

使用VBA代码从右键菜单运行多个宏

要通过右键菜单运行工作簿中的宏代码，以下步骤可能会对您有所帮助，请按如下操作：

1. 按住 Alt + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2. 然后，在左侧的项目窗格中双击 ThisWorkbook，并将以下VBA代码复制粘贴到空白模块中。

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

3. 仍然在 Microsoft Visual Basic for Applications 窗口中，请点击 插入 > 模块，并将以下代码粘贴到该模块中。

Private Sub LoadMacro()
Dim xArrMenu As Variant
Dim xStrLine, xSreBtnName As String
Dim xObjCBCF, xObjCntrAll As CommandBarControl
Dim xObjCBCs As CommandBars
Dim xObjCBBtn As CommandBarButton
Dim xIntLine, xFNum As Integer
Dim xObjComponent As Object
Run "ClearMacro"
Set xObjCBCF = Application.CommandBars("Cell").Controls.Add(msoControlPopup, before:=1)
xObjCBCF.Caption = " Run Macro "
xObjCBCF.BeginGroup = False
For Each xObjComponent In ActiveWorkbook.VBProject.VBComponents
    If xObjComponent.Type = 1 Then
        For xIntLine = 1 To xObjComponent.CodeModule.CountOfLines
        xStrLine = xObjComponent.CodeModule.Lines(xIntLine, 1)
        xStrLine = Trim(xStrLine)
            If (InStr(xStrLine, "()") > 0) And (Left(xStrLine, 11) = "Private Sub" Or Left(xStrLine, 3) = "Sub") Then
            xSreBtnName = ""
            If "Private Sub" = Left(xStrLine, 11) Then
                xSreBtnName = Trim(Mid(xStrLine, 12, InStr(xStrLine, "()") - 12))
            ElseIf "Sub" = Left(xStrLine, 3) Then
               xSreBtnName = Trim(Mid(xStrLine, 4, InStr(xStrLine, "()") - 4))
            End If
            If xSreBtnName <> "" And xSreBtnName <> "RightClickReset" And xSreBtnName <> "LoadMacro" And xSreBtnName <> "ActionMacro" Then
                Set xObjCBBtn = xObjCBCF.Controls.Add
                With xObjCBBtn
                    .FaceId = 186
                    .Style = msoButtonIconAndCaption
                    .Caption = xSreBtnName
                    .OnAction = "ActionMacro"
                End With
            End If
            End If
        Next xIntLine
    End If
Next xObjComponent
End Sub
Private Sub ClearMacro()
On Error Resume Next
CommandBars("Cell").Controls(" Run Macro ").Delete
Err.Clear
CommandBars("Cell").Reset
End Sub
Private Sub ActionMacro()
On Error GoTo Err1
With Application
Run .CommandBars("Cell").Controls(1).Controls(.Caller(1)).Caption
End With
Exit Sub
Err1:
    MsgBox "Invalid"
End Sub

4. 粘贴代码后，点击 工具 > 引用，会弹出一个 References-VBAProject 对话框，然后在 可用引用 列表框中勾选 Microsoft Visual Basic for Applications Extensibility 5.3 选项，见截图：

5. 然后点击 确定 退出对话框，现在，您应该将此工作簿保存为 Excel 启用宏的工作簿 格式，见截图：

6. 最后，请重新启动工作簿以使代码生效，现在，当您右键单击单元格时，右键菜单中会插入一个 运行宏 选项，并且工作簿中的所有宏都会列在子菜单中，如下图所示：

7. 然后，您只需单击即可运行代码。