Sum selection - like in status bar

[Guest]

At the bottom of an excel screen, in the status bar, the Sum of selected
cells automatically appears.
I'd like to have exactly that appear in a cell on the worksheet.
I've tried things along the lines of sum(selection) and made it volatile,
but everything I've tried misbehaves one way or another.

 

[JE McGimpsey]

One way:

Put this in your worksheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("A1").Value = Application.Sum(Selection)
End Sub

This will give the same result as the status bar unless the selection
includes the summary cell (if it did update when the summary cell was
selected, you'd get an overflow due to the "infinite" loop).

If this is what you've already tried, post back with why it won't work
for you.

 

[Bernie Deitrick]

Adrian,

Right click on the sheet tab, select "View Code" and paste the code below
into the window that appears.

The sum of the selected cells will appear in cell A1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Application.Sum(Target)
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Dear Bernie,
I got something that behaved similarly (though not as well as this - thank
you), but there is an important failing...

Set up one column with values such as A,B,C,A,B,C and another column with
assorted numerical values. Turn on Auto-filter and display only the rows with
A. Select the corresponding numbers by dragging over the visible numbers.
The sum in the status bar and the sum from your macro will be different.

 

[Sharad Naik]

In VBA insert a module and there copy following code:

Public Function SumSel()
Dim c
Application.Volatile
For Each c In Selection.Cells
If Not c.Formula = "=SumSel()" Then
If IsNumeric(c.Value) Then
SumSel = SumSel + c.Value
End If
Else
SumSel = ""
Exit Function
End If
Next
End Function


Then In Thisworkbook Class, copy following code:

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

Now in the cell where you want to see the total of selection manually enter
formula by typing as under

=SumSel()

Sharad

 

[Bernie Deitrick]

Adrian,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Application.Sum(Target.SpecialCells(xlCellTypeVisible))
End Sub

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

One way:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next 'in case no visible cells
Range("A1").Value = _
Application.Sum(Target.SpecialCells(xlCellTypeVisible))
On Error GoTo 0
End Sub

 

[Sharad Naik]

To work with your auto filter requirement,
the SumSel function can be modified as under:

Public Function SumSel()
Dim c
Application.Volatile
For Each c In Selection.Cells
If Not c.Formula = "=SumSel()" Then
If IsNumeric(c.Value) And Not c.EntireRow.Hidden Then
SumSel = SumSel + c.Value
End If
Else
SumSel = ""
Exit Function
End If
Next
End Function

Sharad

 

[Bernie Deitrick]

JE,

Is it possible to change your selection manually without selecting at least
one visible cell?

Bernie

 

[JE McGimpsey]

yup. Hide rows 2:10, say. In the Name Box, type J5.

 

[Bernie Deitrick]

Ah, yes. Thanks, JE. I rarely use F5 - it's too far of a reach... ;-)

Bernie

 

[Guest]

That'll do nicely.
Thanks.

Adrian,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Application.Sum(Target.SpecialCells(xlCellTypeVisible))
End Sub

HTH,
Bernie
MS Excel MVP