Modify VBA Code To Run As Two Separate Macros to assign Shortcuts

[im2bz2p345]

Hi all,

I am an Excel 365 (32-bit) user that can't seem to get this bit of code to work properly after all sorts of effort.

Here is my current code:

Public Sub Create_Shortcuts()

    'Ctrl + Shift + C
    Application.OnKey "^+C", "StoreSum"
    'Ctrl + Shift + V
    Application.OnKey "^+V", "PasteSum"

End Sub

Sub StoreSum()
Dim mySum As String

mySum = WorksheetFunction.Sum(Selection)

SaveSetting "SelectionSum", "Section1", "Key1", mySum

End Sub

Sub PasteSum()

ActiveCell.Value = GetSetting("SelectionSum", "Section1", "Key1")

End Sub

Instead of assigning a key combination using the Application.OnKey command, is it possible to split this up into two separate macros:
1. To copy the sum of numbers that I select
2. Paste-special that sum as values

I would somehow like for this to show up in the Macros windows when you press Alt + F8. Screenshot below.


Any help would be greatly appreciate because I could not get this to work in my version of Excel as the code stands. Thank you!!

~ Im2bz2p345

 

[Saurabhj]

Hi, To assign a shortcut to already created procedures -

1. Go to Developer tab and click on Macro option.
2. Select Your first Subroutine (StoreSum) from the dropdown and click on Option button.
3. Enter your Shortcut Key in the box (press Shift+c) and click Ok.
4. Do the same steps for second Macro.

 

[im2bz2p345]

Thank you Saurabhj,

I was initially trying to put the code in an Excel add-on, but that didn't seem to work. After enabling the add-on, for some reason, the subroutines wouldn't show up in the Macro key assignment window.

I now altered my startup Excel file called "Personal.xlsm" (in the directory C:\Users\User_name\AppData\Roaming\Microsoft\Excel\XLSTART) by adding only the code below into the Module1

Sub StoreSum()
Dim mySum As String

mySum = WorksheetFunction.Sum(Selection)

SaveSetting "SelectionSum", "Section1", "Key1", mySum

End Sub

Sub PasteSum()

ActiveCell.Value = GetSetting("SelectionSum", "Section1", "Key1")

End Sub

Upon restarting Excel (which loads the Personal.xlsm file), I was then able to assign the appropriate shortcuts to the subroutines.

I wonder what caused the difference. Anyway, now it works fine. I marked you answer as the solution.

Appreciate your help,

~ Im2bz2p345

 

[Saurabhj]

It's great that the problem is resolved. Thanks for sharing feedback.