Display Status Bar Function Results in VBA

[L. Howard Kittle]

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

 

[Greg Wilson]

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

 

[Bob Phillips]

Here is an alternative

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayStatusBar = True
On Error Resume Next
Application.StatusBar = "Average=" & Application.Average(Selection) & _
"; " & _
"Count=" & Application.CountA(Selection) & "; " & _
"Count nums=" & Application.Count(Selection) & _
"; " & _
"Sum=" & Application.Sum(Selection) & "; " & _
"Max=" & Application.Max(Selection) & "; " & _
"Min=" & Application.Min(Selection)
End Sub


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[L. Howard Kittle]

Thanks Greg and Bob. Very interesting solutions. I appreciate the
responses.

Regards,
Howard

 

[Tom Ogilvy]

Here is a link to some code I posted way back in 1998 that shows how to
change the selection (and restore it)

http://tinyurl.com/2zzlve

in case you wanted to loop through the various functions.

 

[L. Howard Kittle]

Thanks Tom, I'll give it a go.

Regards,
Howard

Here is a link to some code I posted way back in 1998 that shows how to
change the selection (and restore it)

http://tinyurl.com/2zzlve

in case you wanted to loop through the various functions.