Right-Click menu and sum of ranges

[Mathias]

Hi everyone,

I know that If I select a range in a spreasheet (continuous, or b
Ctrl+Clicking a few cells) I can see the sum of the values in the rang
on the Status bar, but would it be possible to have this info somewher
else?
This is basically the only reason I have my Status bar visible, and a
I like my Excel as unclutered as possible, I would like to get rid o
it
Ideally, I would love to be able to view this on the Right-Click menu
but I dont think it is possible without somebody writing an add-in
Does one like this exist?

Thanks for your help,
Mathia

 

[Bob Umlas]

Try this:

Right-click the Excel LOGO near the left of the file menu, select View Code,
enter this, which will put the sum at the bottom of the right click menu.
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
With Application.CommandBars("Cell")
.Reset
With .Controls.Add(msoControlButton)
.Caption = "Sum of selected cells is " &
Application.Sum(Selection)
End With
End With
End Sub

Bob Umlas
Excel MVP

 

[Mathias]

It worked!
Thanks a lot.
Cheers,
Mathia

 

[Dave Peterson]

I like Bob's idea, but I don't like resetting my toolbar.

I'd make this minor change:
Option Explicit

Private Sub Workbook_Deactivate()

With Application.CommandBars("Cell")
On Error Resume Next
.FindControl(Tag:="__Sum__").Delete
On Error GoTo 0
End With

End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)

Dim res As Variant
On Error Resume Next
res = Application.Sum(Selection)
On Error GoTo 0

If IsError(res) Then
res = "Error"
End If

With Application.CommandBars("Cell")
On Error Resume Next
.FindControl(Tag:="__Sum__").Delete
On Error GoTo 0
With .Controls.Add(msoControlButton)
.Caption = "Sum of selected cells is: " & res
.Tag = "__Sum__"
End With
End With
End Sub


And the workbook_deactivate will make sure that the sum doesn't appear on the
cell toolbar in other workbooks.

 

[cmoore]

This is really neat but how do I remove the Menu item created in Bob'
example??

 

[Dave Peterson]

In Bob's code, he reset it.

You can do it manually:
Hit alt-f11 to get to the VBE
hit ctrl-G to see the immediate window

type
application.commandbars("cell").reset
and hit enter.