Assign Alt+A to a Macro

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
Is there a way to assign Alt+A to use as a shortcut to run the following macro?

VBA Code:
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

VBA Code:
Sub CopyShortCut()

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

End Sub

Then I added the following in ThisWorkBook

VBA Code:
Option Explicit

Private Sub Workbook_Open()

Call Module3.CopyShortCut

End Sub

Thank you very much in advance for any assistance!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Apologies - I had forgotten that the Ribbon does indeed interfere with using the Alt key in shortcuts.
 

jrjobe

New Member
Joined
Feb 3, 2012
Messages
29
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,491
Messages
5,831,998
Members
430,103
Latest member
BIGGAZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top