Sum cells

[D]

Is there a way to see the total when a number of cells are highlited? I know
about the status bar on the bottom, but it would be helpful if there were
someway to right click or have a balloon show the total.
thanks,

 

[Mike Rogers]

D

The status bar shows you Average, Count, Count Nums, Min, Max, & Sum. All
for the selected cells. Right click in the right end of the status bar and
select the function you want for your selected cells.

Mike Rogers

 

[D]

Thanks for the reply, i understand how this works as you explained, but what
I am looking for is a way to highlight cells and have the answer there,
instead of checking the status bar. Perhaps a right click and a popup would
be fine.

 

[Gord Dibben]

Copy/paste this code to your worksheet.

Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Range, Cancel As Boolean)
Set rng = Selection
MsgBox "Sum is: " & WorksheetFunction.Sum(rng)
Cancel = True
End Sub

Select some cells with left button then right-click to see the sum in a message
box.


Gord Dibben MS Excel MVP

 

[D]

Thanks, very nice work, except now my other right click functions are
missing. i.e. cut,paste,etc. Any suggestions?

 

[Gord Dibben]

What...........cake and eat it too<g>

Remove the "Cancel = True" which was put in for testing.

You will still have to cancel the message box but the regular right-click will
then appear.

A bit annoying for me.

I prefer the Status bar.


Gord

 

[D]

Thanks again, this works good for my current project.

d

 

[Gord Dibben]

If you are still watching this thread............

You can add a command to the right-click menu that will do the same thing but
not pop-up the message box every time you right-click.

In Thisworkbook module of your workbook or Personal.xls enter this code.

Private Sub Workbook_Open()
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Sum Cells"
.OnAction = "SumUp"
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Sum Cells").Delete
End Sub

In a general module of your workbook or Personal.xls enter this macro.

Sub SumUp()
Set rng = Selection
MsgBox "Sum is: " & WorksheetFunction.Sum(rng)
Cancel = True
End Sub


Gord