Displayed SUM in Status Bar

[tomlinsn]

To all,

I know there has to be a way to do this and in the years living in Excel, I haven't figured it out. When I selet a range of numeric values, the sum total in the status bar yields a result. How do I capture this value and paste it in another Excel document?

Thank you in advance for your help!
Tom

 

[Jonmo1]

I'm not aware of any native method to do that.

But it can certainly be done with VBA code.
Basic example

Sub test()
Range("B1").Value = Application.Sum(Selection)
End Sub

 

[joeu2004]

When I selet a range of numeric values, the sum total in the status bar yields a result. How do I capture this value and paste it in another Excel document?

AFAIK, you cannot do exactly what you ask for. But the following macro copies the sum of the selected cells to the clipboard. Then you can paste wherever you want.

Sub doit()
Dim c As New MSForms.DataObject
c.settext WorksheetFunction.Sum(Selection)
c.putinclipboard
Set c = Nothing
End Sub

(I am not sure that Set c=Nothing is necessary. But it does not hurt.)

Before we can use the macro, we must select the Microsoft Forms Object Library in VBA. Click on Tools > References, select Microsoft 2.0 Forms Object Library, then click OK.

But if you might have hidden (filtered) data, note that the statusbar function is actually the SUBTOTAL sum. If that is what you want, replace the WorksheetFunction statement above with:

c.settext WorksheetFunction.Subtotal(109,Selection)

 

[tomlinsn]

Thank you so much for your quick response! I have searched through my VBA reference as noted above, but do not find the Microsoft Form Object Library in VBA using your guidance: "Click on Tools > References, select Microsoft 2.0 Forms Object Library, then click OK."

I did find a Microsoft Feeds 2.0 Object Library, but not Microsoft Form Object Library. Is this correct? I am using Excel 2010. Although this is not native to Excel, what you have given me I can assign to a simple key combination and save in my Excel global macros. Please confirm/correct for me. I look forward to your response.

Thank you,
Tom

 

[tomlinsn]

Right after I sent you this question, I googled Microsoft 2.0 Forms Object Library missing and found a link that came back to another post on Mr. Excel (http://www.mrexcel.com/forum/excel-...where-microsoft-forms-2-0-object-library.html) which told exactly how to search my PC to find the .dll needed.

Once found, clicked OK, copied your code into a VBA Module, went into the worksheet and set a key combination to run and it worked flawlessly! Very nice. Now to make this even sweeter, if I want to add this somewhere so that it is always available when I am in Excel, how do I do that?

Thank you again and I look forward to your response!
Tom

 

[joeu2004]

if I want to add this somewhere so that it is always available when I am in Excel, how do I do that?

I prefer to use VBA export/import.

To create the file, in VBA, click File > Export File and follow the dialog prompts.

To import the VBA module into a new Excel file, in VBA, click File > Import File and follow the dialog prompts.

-----

Alternatively, put the VBA procedure into the personal.xlsb file.

I never can remember how to create personal.xlsb from scratch; I even have trouble remembering the name. There is a limit to what my addled brain can retain.

So I cheat. In Excel, I click Record Macro (icon in the lower left of the status bar), select personal.xlsb in the dialog box, then click Stop Recording (same icon).

Then in VBA, select the module in the personal.xlsb project, press ctrl+A to select the empty recorded macro, copy the copy-to-clipboard macro and paste into the module, and press ctrl+S to save personal.xlsb.

Then every time you start Excel, personal.xlsb is opened in VBA, making the copy-to-clipboard macro available.

 

[tomlinsn]

That is PERFECT! My brain has the same problem remembering the native Excel filename or the location of where that file resides -- specifically, the XLSTART file seems to move location from version to version. Thank you for your guidance and assistance on this task...one of my Excel bucket list problems solved ! Thank you to everyone!!