Remembering Macro Shortcuts when open new file...is it possible

[PP3321]

Dear Chandoo Community,

I am sorry if this is asked before...

Everytime I assign keyboard shorcut to macro, it is not remembering.
I have to assign it again when opening new files...

Is there anyway that Excel remembers the shorcut...?

Thank you...

 

[Monty]

Hello PP3321


Good question, even i faced same issue at work place..But this can seen other way round..better and easy.

Open excel and copy your "Autofit" code into module and follow the steps
The steps are slightly different in Excel 2007:

1. Click the Office button, Prepare, and then Properties. Excel displays the Document Information Panel just below the ribbon and just above the worksheet.
2. Make sure the Title field is filled in. What you enter here will appear in the Add-Ins dialog box used by Excel.
3. Make sure the Comments field is filled in. What you enter here will appear in the description area of the Add-Ins dialog box used by Excel.
4. Close the Document Information Panel.
5. Press F12. Excel displays the Save As dialog box.
6. Using the Save As Type pull-down list, specify a file type of Excel Add-In (*.xlam).
7. Specify a name for your add-in file in the File Name field.
8. Click on Save. Your add-in file is created.
9. Close the workbook you just saved as an add-in.

To assign a macro to a custom group on a custom Ribbon tab, you follow these steps:

1. Choose File→Options and then click the Customize Ribbon tab in the Excel Options dialog box (or press Alt+FTC).
2. Excel displays the Customize Ribbon pane in the Excel Options dialog box.
3. Click Macros in the Choose Commands From drop-down list box on the left.
4. Excel lists the names of all the macros created in the current workbook in the Choose Commands From list box.
5. Click the name of the custom group on the custom tab to which you want to add the macro in the Main Tabs list box on the right.
6. If you haven’t already created a custom tab and group for the macro or need to create a new one,

To assign a macro to a custom button on the Quick Access toolbar, follow these steps:

1. Click the Customize Quick Access Toolbar button at the end of the Quick Access toolbar and then click More Commands on its drop-down menu.
2. Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected.
3. Click Macros in the Choose Commands From drop-down list box.
4. Excel lists the names of all the macros created in the current workbook in the Choose Commands From list box.
5. Click the name of the macro to add to a custom button on the Quick Access toolbar in the Choose Commands From list box and then click the Add button.
6. Click OK to close the Excel Options dialog box.

Though it looks lengthy but it's simple and remains permanently in any workbook..Rather then remembering the shortcut keys..

Hope you like this.

 

[PP3321]

@Monty
Thank you for your tip.
I have never thought of using Add-ons...!

I googled further, and I found this approach to
use Application Object to assign Shortcut.

Saving these in ThisWorkbook (not in module)
seems to be working fine every time I open a new file...

Sub ShortCuts()
    Application.MacroOptions Macro:="AutoFit", ShortcutKey:="f"
End Sub

*Another way to use Application object (.Onkey)

Sub Sample4()
Application.OnKey "f", "AutoFit"
End Sub

 

[PP3321]

@Monty
Sorry it is not working. I get this error message.
Add-on approach may be the only way...

 

[Monty]

Good try...But add ins are my fav, as it will stick to your workbook forever when ever you load the file.

 

[Monty]

Try This using Workbook open event!!!

Private Sub Workbook_Open()
Application.MacroOptions Macro:="AutoFit", Shortcut Key:="f"
End Sub

 

[PP3321]

@Monty
Thank you!

I added those to overcome the error of
'Cannot edit the macro on a hidden workbook'

Windows("PERSONAL.XLSB").Visible = True
Application.MacroOptions Macro:="AutoFit", ShortcutKey:="f"
Windows("PERSONAL.XLSB").Visible = False