Copy data from the Status Bar

[MCook]

Can you copy the data from the Status Bar to another cell in the spread
sheet. Example, select SUM on the status bar, select a range of cells, status
bar shows the SUM, can that number be copied?
Thanks,

 

[Gary''s Student]

The status bar is just a display based on selection. You can get the same
thing with a helper cell and a little VBA. Let's use cell Z100 as the helper.

Sub qwerty()
Range("Z100").Value = Application.WorksheetFunction.Sum(Selection)
Range("Z100").Copy
End Sub

Select the cells. Run the macro. Go elsewhere. Do a paste.

 

[Gord Dibben]

I don't think there is away to capture that value directly.

But there is the Autosum function which does the same thing.

Select a bunch of cells and hit the Autosum icon on the Toolbar.

If you wanted the value to go to a specific cell you would use the normal
SUM function or VBA

Sub Sum_Range()
Set rng = Selection
Range("A1").Value = WorksheetFunction.Sum(rng)
End Sub

Assign the macro to a button.


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

Hi,

You might want a function which returns the equivalent of the Status bar
calculations. In this case you can return any of the 6 calculation.

The following function will calculate the specified result for any selected
range.

Function SBar(T As Integer) As Double
Application.Volatile
With WorksheetFunction
Select Case T
Case 1: SBar = .Sum(Selection)
Case 2: SBar = .Average(Selection)
Case 3: SBar = .Count(Selection)
Case 4: SBar = .CountA(Selection)
Case 5: SBar = .Max(Selection)
Case 6: SBar = .Min(Selection)
Case Else: SBar = "N/A"
End Select
End With
End Function

To add this code to a workbook press Alt+F11 and select your file in the
Project explorer in the top left side of the screen. Choose Insert, Module.
Put the code in the resulting module.

Also add the following code to the thisWorkbook object:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
ActiveSheet.Calculate
End Sub

In the spreadsheet enter a formula like
=SBar(1)
You will get a circular reference error but ignore it.