About the autocalculate in the status bar...
 

Excel 2003

I take it there's no way VBA method to access control of the
AutoCalculate feature in the status bar that shows avg/sum/etc upon
selecting cells of numbers... right?

I wanted to create a button that would make it toggle from sum to avg
instead of the conventional right-clicking it and then choosing the
function manually.

If not, I'm thinking of making my own display such as a modeless form
or toolbar.

Any experience with this or any ideas?

Thanks,
Melina

About the autocalculate in the status bar...
 

How about you see several functions at once?

Forgot who posted this.......Jim Rech?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim s As String
Dim wfn As WorksheetFunction
Set wfn = Application.WorksheetFunction
On Error GoTo errH:
If wfn.Count(Target) < 2 Then
s = ""
Else

s = "Sum= " & wfn.Sum(Target) & " " & _
"Avg= " & wfn.Average(Target) & " " & _
"Min= " & wfn.Min(Target) & " " & _
"Max= " & wfn.Max(Target)

End If
errH:
Application.StatusBar = s
End Sub


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 15:51:53 -0700 (PDT), Mel wrote:

Excel 2003

I take it there's no way VBA method to access control of the
AutoCalculate feature in the status bar that shows avg/sum/etc upon
selecting cells of numbers... right?

I wanted to create a button that would make it toggle from sum to avg
instead of the conventional right-clicking it and then choosing the
function manually.

If not, I'm thinking of making my own display such as a modeless form
or toolbar.

Any experience with this or any ideas?

Thanks,
Melina

About the autocalculate in the status bar...
 

Too cool! Much simpler than I was going to build. Saved me a lot of
time. Thanks.

Melina

On Oct 1, 6:55*pm, Gord Dibben <gorddibbATshawDOTca wrote:
How about you see several functions at once?

Forgot who posted this.......Jim Rech?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
* * * * * * * * * * * * ByVal Target As Range)
Dim s As String
Dim wfn As WorksheetFunction
* * Set wfn = Application.WorksheetFunction
* * On Error GoTo errH:
* * If wfn.Count(Target) < 2 Then
* * * * s = ""
* * Else

* * * * s = "Sum= " & wfn.Sum(Target) & " " & _
* * * * * * "Avg= " & wfn.Average(Target) & " " & _
* * * * * * "Min= " & wfn.Min(Target) & " " & _
* * * * * * "Max= " & wfn.Max(Target)

* * End If
errH:
* * Application.StatusBar = s
End Sub

Gord Dibben *MS Excel MVP

On Thu, 1 Oct 2009 15:51:53 -0700 (PDT), Mel wrote:
Excel 2003

I take it there's no way VBA method to access control of the
AutoCalculate feature in the status bar that shows avg/sum/etc upon
selecting cells of numbers... right?

I wanted to create a button that would make it toggle from sum to avg
instead of the conventional right-clicking it and then choosing the
function manually.

If not, I'm thinking of making my own display such as a modeless form
or toolbar.

Any experience with this or any ideas?

Thanks,
Melina

About the autocalculate in the status bar...
 

The originator will be happy to hear that.

Glad I could pass it on.


Gord

On Thu, 1 Oct 2009 19:45:43 -0700 (PDT), Mel wrote:

Too cool! Much simpler than I was going to build. Saved me a lot of
time. Thanks.

Melina

On Oct 1, 6:55*pm, Gord Dibben <gorddibbATshawDOTca wrote:
How about you see several functions at once?

Forgot who posted this.......Jim Rech?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
* * * * * * * * * * * * ByVal Target As Range)
Dim s As String
Dim wfn As WorksheetFunction
* * Set wfn = Application.WorksheetFunction
* * On Error GoTo errH:
* * If wfn.Count(Target) < 2 Then
* * * * s = ""
* * Else

* * * * s = "Sum= " & wfn.Sum(Target) & " " & _
* * * * * * "Avg= " & wfn.Average(Target) & " " & _
* * * * * * "Min= " & wfn.Min(Target) & " " & _
* * * * * * "Max= " & wfn.Max(Target)

* * End If
errH:
* * Application.StatusBar = s
End Sub

Gord Dibben *MS Excel MVP

On Thu, 1 Oct 2009 15:51:53 -0700 (PDT), Mel wrote:
Excel 2003

I take it there's no way VBA method to access control of the
AutoCalculate feature in the status bar that shows avg/sum/etc upon
selecting cells of numbers... right?

I wanted to create a button that would make it toggle from sum to avg
instead of the conventional right-clicking it and then choosing the
function manually.

If not, I'm thinking of making my own display such as a modeless form
or toolbar.

Any experience with this or any ideas?

Thanks,
Melina