I don't know how to retrieve the value from this window directly without a
lot of API code at least. Maybe someone else knows something simpler.

The popup commanbar that appears when you right click over the status bar is
called "AutoCalculate". You can determine which control is selected by
parsing its controls looking for the buttondown state. Having found the
selected control, you can just use the selected control's index value to
select the appropriate function from a list and run the function on the cell
selection. You can also set which control you want to be selected using the
Execute method:-

Sub k()
Dim i As Integer
With Application.CommandBars("AutoCalculate")
For i = 1 To .Controls.Count
If .Controls(i).State = msoButtonDown Then
MsgBox SBVal(i)
Exit For
End If
Next
'.Controls(7).Execute 'Selects the Sum button
End With
End Sub

Function SBVal(mode As Integer) As Single
Dim v As Single
With Application
Select Case mode
Case 1
v = ""
Case 2
v = .Average(Selection)
Case 3
v = Selection.Count
Case 4
v = .Count(Selection)
Case 5
v = .Max(Selection)
Case 6
v = .Min(Selection)
Case 7
v = .Sum(Selection)
End Select
End With
SBVal = v
End Function

Regards,
Greg

"L. Howard Kittle" wrote:

Hello Excel users and Experts,

Excel 2003

Is there a way to display the worksheet status bar values of ADD, MAX,
AVERAGE, et al, via VBA code.

So if I select or reference a few cells with values in them, and have ADD
selected in the status bar can I get a Msgbox or something to display that
value?

And can you change the function of the status bar via VB code. So you could
present something like:

"The SUM of the cells is " 88
"The AVERAGE of the cells is " 33
"The MAX of the cells is " 108

Help on STATUS BAR gives a True or False relevant to displaying the bar, but
no help on displaying the value of the Status Bar.

I have no final goal for this. I found a curious solution in the newsgroup
that used keystrokes to SUM, AVG, etc. same colored formatted cells with the
value displayed in the Status Bar. Wondered if I could reference the status
bar value via code.

I have written my own non-pro code to sum color coded cells on a sheet and I
am aware of Dave McRitchie's true-pro code to do the same thing.

Thanks
Regards,
Howard