SaveSetting & GetSetting VBA Macro Help

[im2bz2p345]

Hi all,

I have a rather simple question regarding the following macro that I received help on creating:

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

I would like to define a keyboard combine (in this macro: To store the Sum, I want to use CTL + Shift + C and to Paste Values the sum, I want to use CTRL + SHIFT + V). Do I just replace the string "Key1" to these commands or how can I go about setting this up properly?

Would appreciate any insight. Thank you in advance!

~ Im2bz2p345

 

[John_w]

Try this:

Public Sub Create_Shortcuts()

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

And in the ThisWorkbook module so that the shortcuts are created automatically when you reopen the workbook:

Private Sub Workbook_Open()
    Create_Shortcuts
End Sub

 

[im2bz2p345]

Thank you @John_w for the quick reply.

Unfortunately, I can't seem to get it to start up correctly - I'm probably doing some silly mistake.

To give some content, I have Excel for Office 365 (32-bit). I have a "Personal.xlsx" file that I created which open ups from this folder (C:\Users\User_name\AppData\Roaming\Microsoft\Excel\XLSTART) everytime that I open Excel because I want the R1C1 setting to remain unchecked and it doesn't stick unless I have a workbook autostart with it unchecked/off.

In my add-ins folder (C:\Program Files (x86)\Microsoft Office\root\Office16\Library), I have the Excel add-in file name SumSelectedCells&PasteSUM.xlam which executes the code in my original post on start up.

What I think you want me to do is add your "Create_Shortcuts()" code above my original code in the add-on and the "Private Sub Workbook_Open()" code in the ThisWorkbook of the add-on. See screenshot below.



I can't seem to get it to work like this.

If you mean, add your 2nd bit of code to the ThisWorkbook of my Personal.xlsx file, I'll have to now save my Personal start-up file as a macro-enabled workbook and it give me an Compile error: Sub or Function not defined error upon start up.

Hope this all makes sense. Thank you for any further help.

~ Im2bz2p345

 

[John_w]

I have the Excel add-in file name SumSelectedCells&PasteSUM.xlam which executes the code in my original post on start up.

In that case just include a call to Create_Shortcuts in the code which executes the code in your original post and scrap the call in ThisWorkbook Workbook_Open.

 

[im2bz2p345]

That worked! Ty once again @John_w for your quick help. I marked your original post as the solution.

I basically removed my original add-in from my startup directory, opened a new Workbook, created a new module, inserted the following modified VBA code [with your Public Sub Create_Shortcuts() code at the top], then saved the file as my new SumSelectedCells&PasteSUM.xlam file. I put that file in the same add-in directory (C:\Program Files (x86)\Microsoft Office\root\Office16\Library) and everything works as expected!

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

Just want to document all the steps here incase I need to refer back to it or anyone else needs the information down the road.

Appreciate you taking the time to address my questions,

~ Im2bz2p345

 

[im2bz2p345]

Scratch that @John_w ! I can't get it to work with the above code. Does it have it to do anything with the fact that I'm running Excel 365? I remember researching this in the past and there was something about Excel 2011 and then Excel 2013+ being different.

Would love for you to try to see if it working as intended on your end.

~ Im2bz2p345

 

[im2bz2p345]

EDIT: I figured out the problem. If you're on Excel 2013+, you need to enable "Microsoft Forms 2.0 Object Library" by going to: VBA (Alt + F11) -> Tools -> References -> Check mark "Microsoft Form 2.0 Object Library"

See box in red:


I found this from the post by user Dana.D on this website: Can I copy and paste the sum that appears on the status bar when I highlight a range of numbers?

I knew there was something additional that I did before in Excel 2013 to make this work. Glad that I figured it out. Thanks again!

~ Im2bz2p345

 

[im2bz2p345]

My only issue was that I couldn't get the "Microsoft Form 2.0 Object Library" to stay check marked. Following the advice of Dave Peterson from this post: how do I add vba reference to Microsoft Forms 2.0 Object Library?

What I did was add a Userform to my Personal.xlsx file in VBA (ALT + F11) -> Insert -> Userform

Then I had to close out of VBA and save the Personal.xlsx file as a macro-enabled file (Personal.xlsm) in the following directory: C:\Users\User_name\AppData\Roaming\Microsoft\Excel\XLSTART

I then closed out of all instances of Excel completely. Deleted my original Personal.xlsm and make sure only the Personal.xlsm was in the startup folder.

I then restarted Excel and boom! Microsoft Form 2.0 Object Library remained check marked. Since the macro utilizes the 2.0 object library, it now works as expected. I'm simply adding this post as a reference for myself in the future!

~ Im2bz2p345