Hi Martin, hi Garry,

Am Thu, 21 Mar 2013 12:36:45 -0400 schrieb GS:

1 &None
2 &Average
3 &Count
4 C&ount Nums
5 &Max
6 M&in
7 &Sum

This looks like a perfect candidate for an enum if you want to make it
self-documenting in code...

to show all autocalculations in the right click popup menu try following
code (select in right click popup the autocalculation mode you need):

Function AutoCalculate(Optional myRange As Range) As Variant
Dim Ctrl As CommandBarControl
Dim strFunction As String
Dim WF As WorksheetFunction

Set WF = Application.WorksheetFunction
If myRange Is Nothing Then Set myRange = Selection

For Each Ctrl In Application.CommandBars("AutoCalculate").Controls
If Ctrl.State = msoButtonDown Then
strFunction = Ctrl.Caption
Exit For
End If
Next

Select Case strFunction
Case "&None"
AutoCalculate = ""
Case "&Average"
AutoCalculate = WF.Average(myRange)
Case "&Count"
AutoCalculate = WF.Count(myRange)
Case "C&ount Nums"
AutoCalculate = WF.CountA(myRange)
Case "&Max"
AutoCalculate = WF.Max(myRange)
Case "Mi&n"
AutoCalculate = WF.Min(myRange)
Case "&Sum"
AutoCalculate = WF.Sum(myRange)
End Select
End Function


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2