Sum selection - like in status bar
 

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.

Sum selection - like in status bar
 

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.

In article ,
Adrian <Adrian @discussions.microsoft.com wrote:

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.

Sum selection - like in status bar
 

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

"Adrian" <Adrian @discussions.microsoft.com wrote in message
...
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.

Sum selection - like in status bar
 

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.


"Bernie Deitrick" wrote:

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

"Adrian" <Adrian @discussions.microsoft.com wrote in message
...
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.

Sum selection - like in status bar
 

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


"Adrian" <Adrian @discussions.microsoft.com wrote in message
...
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.

Sum selection - like in status bar
 

Adrian,

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

HTH,
Bernie
MS Excel MVP

"Adrian" wrote in message
...
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.


"Bernie Deitrick" wrote:

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

"Adrian" <Adrian @discussions.microsoft.com wrote in message
...
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.

Sum selection - like in status bar
 

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(xlCellTypeVisi ble))
On Error GoTo 0
End Sub


In article ,
Adrian wrote:

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.

Sum selection - like in status bar
 

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
"Sharad Naik" wrote in message
...
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


"Adrian" <Adrian @discussions.microsoft.com wrote in message
...
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.

Sum selection - like in status bar
 

JE,

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

Bernie

"JE McGimpsey" wrote in message
...
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(xlCellTypeVisi ble))
On Error GoTo 0
End Sub

Sum selection - like in status bar
 

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


In article ,
"Bernie Deitrick" <deitbe @ consumer dot org wrote:

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

Sum selection - like in status bar
 

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

Bernie

"JE McGimpsey" wrote in message
...
yup. Hide rows 2:10, say. In the Name Box, type J5.

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

Sum selection - like in status bar
 

That'll do nicely.
Thanks.


"Bernie Deitrick" wrote:

Adrian,

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

HTH,
Bernie
MS Excel MVP

"Adrian" wrote in message
...
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.


"Bernie Deitrick" wrote:

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

"Adrian" <Adrian @discussions.microsoft.com wrote in message
...
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.