Hi,


When I mark/select a range of cells, I can see the SUM of all the cell
values in the statusbar (as you all probably know).

By right-clicking this SUM-field in the statusbar, I can also choose to
view MIN or MAX or some other calculation on the selected cells.

But, how to I set this programmically by VBA?

Say, the setting is set to show MAX, but I want to change it into
showing SUM - using VBA code...

How to do this?


Thanks,

CE

Charlotte E. wrote:
Hi,


When I mark/select a range of cells, I can see the SUM of all the cell
values in the statusbar (as you all probably know).

By right-clicking this SUM-field in the statusbar, I can also choose to
view MIN or MAX or some other calculation on the selected cells.

But, how to I set this programmically by VBA?

Say, the setting is set to show MAX, but I want to change it into
showing SUM - using VBA code...

How to do this?


Thanks,

CE


you have to calculate it by yourself and display string on status bar.

use selection_change event.

This is Commandbars("AutoCalculate")! You can change its setting from
displaying *Sum* to display *Count* instead as follows...

Commandbars("AutoCalculate").Controls("Count").Exe cute

...and this will persist until you change it again during your session.
It persists when Excel quits and so you --should-- reset it back to the
default *Sum* action at some point.

You can also *hook* this cbar as the following example demos:

Option Explicit

Enum AutoCalc
None = 1: Average: Count: CountNums: Max: Min: Sum
End Enum

Sub AutoCalculate()
Dim Rng As Range, vVal As Variant

Set Rng = Selection.Resize(Selection.Cells.Count - 1)
ActiveCell.ClearContents
With Application.WorksheetFunction
Select Case CommandBars.ActionControl.Index
Case AutoCalc.Average: vVal = .Average(Rng)
Case AutoCalc.Count: vVal = .Count(Rng)
Case AutoCalc.CountNums: vVal = .CountA(Rng)
Case AutoCalc.Max: vVal = .Max(Rng)
Case AutoCalc.Min: vVal = .Min(Rng)
Case AutoCalc.Sum: vVal = .Sum(Rng)
End Select 'Case CommandBars.ActionControl.Index
End With 'Application.WorksheetFunction
ActiveCell.Value = vVal
End Sub

Sub Hook_AutoCalculateMenus()
Dim Ctrl As CommandBarControl
For Each Ctrl In Application.CommandBars("AutoCalculate").Controls
Ctrl.OnAction = "AutoCalculate"
Next
End Sub

Sub Unhook_AutoCalculateMenus()
CommandBars("AutoCalculate").Reset
End Sub

What's interesting is that while the cbar.controls are hooked it still
displays/updates in the statusbar as per whatever mode it was in when
its menus got hooked.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

AWESOME, Garry :-))))

Thanks for a really helpful and inspiring answer .-)


CE


Den 01.06.2013 17:43, GS skrev:
This is Commandbars("AutoCalculate")! You can change its setting from
displaying *Sum* to display *Count* instead as follows...

Commandbars("AutoCalculate").Controls("Count").Exe cute

...and this will persist until you change it again during your session.
It persists when Excel quits and so you --should-- reset it back to the
default *Sum* action at some point.

You can also *hook* this cbar as the following example demos:

Option Explicit

Enum AutoCalc
None = 1: Average: Count: CountNums: Max: Min: Sum
End Enum

Sub AutoCalculate()
Dim Rng As Range, vVal As Variant

Set Rng = Selection.Resize(Selection.Cells.Count - 1)
ActiveCell.ClearContents
With Application.WorksheetFunction
Select Case CommandBars.ActionControl.Index
Case AutoCalc.Average: vVal = .Average(Rng)
Case AutoCalc.Count: vVal = .Count(Rng)
Case AutoCalc.CountNums: vVal = .CountA(Rng)
Case AutoCalc.Max: vVal = .Max(Rng)
Case AutoCalc.Min: vVal = .Min(Rng)
Case AutoCalc.Sum: vVal = .Sum(Rng)
End Select 'Case CommandBars.ActionControl.Index
End With 'Application.WorksheetFunction
ActiveCell.Value = vVal
End Sub

Sub Hook_AutoCalculateMenus()
Dim Ctrl As CommandBarControl
For Each Ctrl In Application.CommandBars("AutoCalculate").Controls
Ctrl.OnAction = "AutoCalculate"
Next
End Sub

Sub Unhook_AutoCalculateMenus()
CommandBars("AutoCalculate").Reset
End Sub

What's interesting is that while the cbar.controls are hooked it still
displays/updates in the statusbar as per whatever mode it was in when
its menus got hooked.

Your welcome! I appreciate the feedback...

Note that using *Reset* only restores OnAction of builtin controls
and/or removes any custom controls you added. If you need to
store/restore the current setting for this cbar you can use the
following function to load it into a global variable so your project
can reset it...

Public glCbarAutoCalcMode&

Function Get_CbarAutoCalcMode&()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars("AutoCalculate").Controls
If ctl.State = msoButtonDown Then _
Get_CbarAutoCalcMode = ctl.Index: Exit For
Next
End Function

...and use it as follows...

glCbarAutoCalcMode = Get_CbarAutoCalcMode
'do stuff
'reset
Commandbars("AutoCalculate").Controls(glCbarAutoCa lcMode).Execute

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

A better working function...

Function Get_AutoCalcCbar_Mode&()
Dim ctl As CommandBarControl
For Each ctl In Application.CommandBars("AutoCalculate").Controls
If ctl.State = msoButtonDown Then
Get_AutoCalcCbar_Mode = ctl.Index: Exit Function
End If
Next
End Function

...which wraps the exit inside the If construct so it doesn't execute
unconditionally!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion