Different workbooks, same macro names and activation shortcuts

Question

I have 2 Workbooks. They contain different data and sheets, but have some same macro names, which are being ran by the same keybord shortcuts. For example: both have Sub ArrangeTitles() macro which is being called by Ctrl+Shift+R. The vba codes inside these Subs are different.

The problems start when both Workbooks are open and the user presses Ctrl+Shift+R, which sometimes runs the wrong Sub (the "other Workbook's Sub"), and this is according to the order of opening the Workbooks.

My goal is to be able to activate the right macro which belongs to the right Workbook, without changing any names or shortcuts.

I have looked for a solution for this all around the web with no success. So I figured out some-kind of bypass solution which works well. I will soon add the answer here.

Answer 1

The Solution is based on attaching each macro to its ActiveWorkbook.Name.

For each Workbook, and every "problematic" macro (for example: my Sub ArrangeTitles), do the following:

At Excel "View" tag -> Macros:

1. Disconnect the Sub ArrangeTitles from its keyboard activation Ctrl+Shift+R.

At the VBA Code:

2. Optional: change the ArrangeTitles Sub into Function.

3. Optional: Add new Module to contain al my new "connection Subs" such as the following:

4. Add:

Sub Ctrl_Shift_R()
    Dim strPath As String
    strPath = "'" & ActiveWorkbook.Name & "'" & "!ArrangeTitles"  ' The target macro within it's current wWorkbook
    Application.Run (strPath)
End Sub

And back to Excel "View" tag -> Macros:

5. Connect the macro Ctrl_Shift_R to the keyboard activation: Ctrl+Shift+R.

6. Save.

7. Instruct the user to click somewhere in the workbook before using Ctrl+Shift+R.