excel - 使用相同快捷方式打开多个 Excel 文件的问题

Question

我有多个 Excel 文件，它们的宏分配了相同的快捷方式。当我同时打开这些文件并通过快捷方式运行宏时，通过在VBE中调试，我发现它总是会运行第一个打开的文件中的宏而不是宏本身。如何解决？

标签： excelvbashortcut

Answer 1

我的建议是以编程方式分配快捷方式，并在工作簿变为活动/不活动时动态启用/禁用它们。通过这种方式，您可以确保一次仅在一个工作簿（即活动工作簿）中启用快捷方式。

这是我解决它的方法：

把它放在ThisWorkbook模块中：

Private Sub Workbook_Activate()
    'Debug.Print "Workbook got activated"
    Call KeyboardShortcuts(True)
End Sub
Private Sub Workbook_Deactivate()
    'Debug.Print "Workbook got deactivated"
    Call KeyboardShortcuts(False)
End Sub

这可以放在任意代码模块中：

Function KeyboardShortcuts(assign As Boolean)
    ' There is a problem when you assign keyboard shortcuts to macros through the macros menu:
    ' When there are multiple Excel workbooks open in the same instance of the Excel application,
    ' it is possible that when you enter a keyboard shortcut in workbook A, it gets captured from workbook B.
    ' This can lead to undesired results.
    ' So we do it like this:
    ' keyboard shortcuts are enabled when the workbook gets activated and
    '              they are disabled when the workbook gets deactivated.
    Dim Keydef As String
    Dim Keys() As String
    Dim item As Variant
    Dim pair() As String
    
    Keydef = "+^W:Function1_KB" & _
       "|" & "+^D:Function2_KB" & _
       "|" & "+^F:Function3_KB" & _
       "|" & "+^G:Function4_KB" & _
       "|" & "+^L:Function5_KB" & _
       "|" & "+^P:Function6_KB" & _
       "|" & "+^T:Function7_KB" & _
       ""

    Keys = Split(Keydef, "|")
    
    For Each item In Keys
        pair = Split(item, ":")
        
        If assign = True Then
            ' Enable the keyboard shortcut:
            Application.OnKey pair(0), pair(1)
        Else
            ' Disable the keyboard shortcut:
            Application.OnKey pair(0)
        End If
    Next
    
End Function

Function Function1_KB()
   Debug.Print "You have pressed Shft+Ctrl+W"
End Function
...
Function Function7_KB()
   Debug.Print "You have pressed Shft+Ctrl+T"
End Function