Macro keyboard shortcut doesn't work

[Yamezz]

I have a couple of macros in my PERSONAL.XLSB file. Both are assigned a keyboard shortcut, but only one works with the shortcut. It worked briefly, but not anymore.
This works:

Sub ShowAllCTRLEND()
'
' Keyboard Shortcut: Ctrl+q
'
    If ActiveSheet.FilterMode = True Then
    
    ActiveSheet.AutoFilter.ShowAllData
    Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
    Cells(ActiveCell.Row, 1).Select
    ActiveCell.Offset(1, 3).Activate
    
    Else
    Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
    Cells(ActiveCell.Row, 1).Select
    ActiveCell.Offset(1, 3).Activate
    End If

End Sub

This code will run fine if evoked manually, but will not run with the shortcut, despite being listed as a shortcut in the 'options' of the macro menu.

Sub AlignRight()
'
' AlignRight Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

What could be the cause?

 

[RoryA]

Perhaps you have another macro using that shortcut?

 

[Yamezz]

Perhaps you have another macro using that shortcut?

Not that I can see.

 

[RoryA]

Do you have any add-ins loaded? Does anything happen when you press the shortcut keys?

 

[Yamezz]

No add-ins.
When I press CTRL + q, my ShowAllCTRLEND() macro runs fine.
When I press CTRL + SHIFT + R, nothing happens at all.

 

[GWteB]

If you've moved that macro to another code module its shortcut will not stick.
To "restore" the assignment of the shortcut keys the OnKey method can be used.

Public Sub Yamezz()

    Const KEYALT   As String = "%"
    Const KEYCTRL  As String = "^"
    Const KEYSHIFT As String = "+"

    Excel.Application.OnKey KEYSHIFT & KEYCTRL & "r", "AlignRight"
End Sub

 

[Yamezz]

I copied the code and ran it. Now when I press CTRL + SHIFT + R I receive the error:
Cannot run the macro "C:\Users\James\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB'!AlignRight'. The macro may not be available in this workbook or all macros may be disabled.
But when I press CTRL + q, my ShowAllCTRLEND() macro runs fine.

 

[RoryA]

Which workbook and module is the problem code in?

 

[GWteB]

I'm not able to reproduce your issue, so not sure what's going on.
To begin with, move your macro (cut & paste) forth and back between different modules in your PERSONAL.XLSB workbook. That way we're sure the connection between shortcut keys and macro is lost. Now save the PERSONAL.XLSB, quit Excel, restart Excel and open a new workbook. At this point there are two things you can do in the context of a possible solution to your problem.

1) In Excel press Alt F8, choose your macro (within PERSONAL.XLSB), press Options button and reassign the desired shortcut keys. Quit Excel and save PERSONAL.XLSB when you're prompted to do so. This should work. If it doesn't then opt for 2).

2) Every time Excel is started and PERSONAL.XLSB is automatically opened, run a macro that takes care of the shortcut assignment. In this case:
This goes in the ThisWorkbook module of PERSONAL.XLSB:

Private Sub Workbook_Open()
    Yamezz
End Sub

This goes in a standard module of PERSONAL.XLSB:
Note that the assignment consists of the name of the code module in which the desired macro is located. Change this name according to your situation.

Public Sub AlignRight()
'
' AlignRight Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Public Sub Yamezz()

    Const KEYALT   As String = "%"
    Const KEYCTRL  As String = "^"
    Const KEYSHIFT As String = "+"

    Excel.Application.OnKey KEYSHIFT & KEYCTRL & "R", "'PERSONAL.XLSB'!Module1.AlignRight"  ' << change Module name accordingly
End Sub

 

[Yamezz]

Method 1 did not work.
I tried something a little different though, that seems to have stuck.
Instead of my macros being in individual modules, I created a module named "Custom", then cut and pasted my handful of little macros all into the "Custom" module and reassigned the keyboard shortcuts. It all appears to be working so far.

Thanks for the help guys.