Assign Alt+A to a Macro

[jrjobe]

Is there a way to assign Alt+A to use as a shortcut to run the following macro?

Sub COPY(control As IRibbonControl)

    ActiveCell.Offset(0, 0).Range("A1:F1").Select
    Application.CutCopyMode = False
    Selection.COPY
    Application.Run "BACK"
    ActiveCell.Offset(0, 0).Range("A1:F1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Select
    Windows("DATABASE.XLSM").Activate
    Application.CutCopyMode = False

End Sub

I have this macro assigned to a customUI Ribbon, so when I click the button on the ribbon, it will copy cells A:F of whichever cell I select in column A and paste it into another workbook that is open. However, this is used quite frequently and I would like to assign Alt+A to it. I tried the Application.OnKey method, but it still wouldn't do anything. I prefer to use Alt+A due to the convenient location and proximity of those key combos. I've spent quite a while trying to figure this one out, but am finally giving up to ask for some assistance.

Here is what I used in Module3

Sub CopyShortCut()

Application.OnKey "%{A}", "Admin.COPY" 'I also tried without using Admin.

End Sub

Then I added the following in ThisWorkBook

Option Explicit

Private Sub Workbook_Open()

Call Module3.CopyShortCut

End Sub

Thank you very much in advance for any assistance!

 

[maabadi]

You can't Use because ALT+A is Shortcut for Select Data Tab on Excel, But You Can Use CTRL+ALT+A:
GO to Developer Tab, Macros, Select The Macro you want, Then Click on Option at the Right side of window, And with Holding ALT, Click on A Key. and Press OK.

 

[jrjobe]

Hmm. Must be a change in Excel 2007+. I'm rebuilding these workbooks that were used in Excel 2003 and earlier that used a custom menu. The same button/macro I mentioned above (minus the "control As" function) used Alt+A to enter single line data. Ctrl+Alt+ is not an available option in the Macro options dialogue box...at least in O365, only Ctrl+Shift.

I guess if there is no other way to make it work, I'll just copy and modify the macro name (the macro above is not available in the macro menu because of the need to use the "control As IRibbonControl") and assign it another shortcut....these old hatters I'm rebuilding this for aren't going to be very happy lol.

 

[jrjobe]

Too late to edit my reply. I'll add to this that Microsoft doesn't mention what key combo won't work, but does say that to use the Application.Onkey method, the %{any key name} is an approved method. I just can't get it to work. % being the Alt key.

Application.OnKey method (Excel)

I'm open to other options that any may have. Surely something is probably incorrectly used in my code, since there aren't any errors, I'm not certain how to troubleshoot it.

 

[RoryA]

Your macro is actually a callback , which is why it has that IRibbonControl parameter. I suggest you move the actual code to a separate routine with no parameters, call that from the callback code you have, and you should then be able to use OnKey with the new macro.

 

[jrjobe]

Thanks, @RoryA! I did try that by copying the macro (named it CopyAltA) and removing the IRibbonControl. I also changed to an alternative key combo and it still wouldn't work.

 

[RoryA]

Apologies - I had forgotten that the Ribbon does indeed interfere with using the Alt key in shortcuts.

 

[jrjobe]

Alright then! I appreciate the update regarding the ribbon. I'll forgo trying to use the Alt key. I ended up assigning Ctrl+Shift+A to the renamed COPY macro (with the IRibbonControl parameter removed) and it works. Just a little wonky feeling lol. Alt+A was a much more natural feeling key combo.