Can I save a keyboard shortcut macro in an add-in module?

[JenniferMurphy]

I just used the macro recorder to generate a macro that will perform the Center Across Selection operation that I seem to be using a lot. It put it in a code module in that workbook, which I then had to save as a macro enabled workbook. And, of course, that macro is not available to other workbooks.

I have an add-in module containing lots of UDSFs. I tried moving the macro to that add-in, but the keyboard shortcut no longer works.

 

[My Aswer Is This]

In Your VBA project window where you see your Module scripts. Drag the script into your Personal Folder. That way it will be available in all workbooks that are Macro enabled
Now to run the script just use your shortcut key

 

[JenniferMurphy]

In Your VBA project window where you see your Module scripts. Drag the script into your Personal Folder. That way it will be available in all workbooks that are Macro enabled
Now to run the script just use your shortcut key

I think my Add-in module may be messed up. There is no "Personal Folder" listed.

Can I just add a module named "Personal Folder"?

Thanks

 

[My Aswer Is This]

I think my Add-in module may be messed up. There is no "Personal Folder" listed.

View attachment 57666
Can I just add a module named "Personal Folder"?

Thanks

I really do not think so. Maybe someone else here on the forum can provide a answer.

 

[JenniferMurphy]

You are correct. VBA won't let me name a module "Personal Folder". I found a website that said to record a macro and choose "Personal Macro Workbook" in the "Store macro in:" field of the "Record Macro" dialog and that would create a Personal Folder, if none exists.

I did that and it worked. I now have a Personal Folder and my macro works. ??

Now my only problem is that I cannot find where Excel stored it. Another website instructed me to open the Trust Center and check the trusted locations for the location of the XLSTART folder. This is what I found:

I went to that location, but that folder is empty. And I do have hidden items checked to view, as you can see.

Any other clues?

 

[My Aswer Is This]

And why do you need to know where it is stored?

Just give your script a shortcut key and launch it like that.
Now like in your image you can see it. If you want to modify it or put a copy in your active workbook, just click on the module and drag it into your active workbook. Now to put the script into your add in module again I do not know how to do that. If you drag it into your active workbook it puts a copy in your active workbook. If you want to put it back give it a new name or right click on it and choose to delete.

I have been doing this for years and never really cared where it's being stored. And a script like this will work in any workbook you have now or in the future. Just use the shortcut key

 

[My Aswer Is This]

If you want to delete the macro in your personal folder.
Just right click on it and choose Remove.

 

[GWteB]

Can I save a keyboard shortcut macro in an add-in module?

Yes, you can, but it's quite a hassle and the shortcuts will not stick when the just recorded macro is moved to another module within the same Add-In. You've discovered that already.
I would go for the OnKey method. That way you can paste any macro code to any standard module within your Add-In.
You just need to add an appropriate line of code to the assign list in the macro below:

This goes in a standard module of your Add-In:

Option Explicit

Public Enum ShortcutToMacroCmd
    Assign
    Cancel
End Enum

Public Sub MacroShortcuts(ByVal argCmd As ShortcutToMacroCmd)
    Const KEYALT   As String = "%"
    Const KEYCTRL  As String = "^"
    Const KEYSHIFT As String = "+"
    
    If argCmd = Assign Then
        '  assign shortcuts to some macros stored in this Add-In
        Excel.Application.OnKey KEYALT & KEYCTRL & "u", "SomeAddInMacro"        ' Alt Ctrl u
        Excel.Application.OnKey KEYALT & "{+}", "SomeOtherAddInMacro"           ' Alt +
        
    ElseIf argCmd = Cancel Then
        '  give shortcuts their original meaning
        Excel.Application.OnKey KEYALT & KEYCTRL & "u"
        Excel.Application.OnKey KEYALT & "{+}"
        
    Else
        ' this should not happen
    End If
End Sub

The code above needs to be invoked when your Add-In gets active. So ...

This goes in the ThisWorkbook module of your Add-In:

Option Explicit

Private Sub Workbook_AddinInstall()
    MacroShortcuts Assign
End Sub
Private Sub Workbook_AddinUninstall()
    MacroShortcuts Cancel
End Sub
Private Sub Workbook_Open()
    MacroShortcuts Assign
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MacroShortcuts Cancel
End Sub

If you wanted to use the PERSONAL.XLSB workbook for your macros assigned to keyboard shortcuts and still need to know its folder on disk ....

Now my only problem is that I cannot find where Excel stored it.

... then open its ThisWorkbook module, paste the code below in there and run it ...

Sub WhereAmI()
    MsgBox Me.Path
End Sub

Hope this helps.