How activate "NUM" thingie?

[Martin Leese]

On the bar at the bottom of my Excel
application, it says "Ready" on the left
and "NUM" on the right. If I right click on
the "NUM" thingie, I can select "COUNT" from
a list. This counts the number of cells
selected and displays "Count=4" on the same
bar.

How do I activate the "NUM" thingie and
select "COUNT" using VBA?

I tried "Record New Macro...", but the
recording is blank. (It also doesn't help
that I have no idea what the "NUM" thingie
is actually called.) I am using Excel 97,
but that shouldn't matter.

 

[GS]

On the bar at the bottom of my Excel

application, it says "Ready" on the left
and "NUM" on the right. If I right click on
the "NUM" thingie, I can select "COUNT" from
a list. This counts the number of cells
selected and displays "Count=4" on the same
bar.

How do I activate the "NUM" thingie and
select "COUNT" using VBA?

I tried "Record New Macro...", but the
recording is blank. (It also doesn't help
that I have no idea what the "NUM" thingie
is actually called.) I am using Excel 97,
but that shouldn't matter.

That's not available to VBA. How you get a count of selected cells in
VBA is by using 'Selection'...

With Selection
Debug.Print .Cells.Count & vbTab _
& .Rows.Count & vbTab & .Columns.Count
End With 'Selection

--
Garry

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

 

[Ben McClave]

Martin,

You could use Application.StatusBar in combination with the Worksheet_SelectionChange event to mimic this. Try this in the sheet's module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.DisplayStatusBar = True
Application.StatusBar = "Count: " & WorksheetFunction.CountA(Target)
End Sub

You could further adapt the above to use other functions (count, sum, min, max, etc.).

One thing to note is that if you select other sheets, the status bar will not change unless the newly selected sheet has the same code. So, you couldadd the above code to all sheets in your workbook, or you may want to include two more events in the worksheet so that the statusbar control is returned to Excel when the sheet is deactivated.

Private Sub Worksheet_Activate()
Application.DisplayStatusBar = True
Application.StatusBar = "Count: " & WorksheetFunction.CountA(Selection)
End Sub

Private Sub Worksheet_Deactivate()
Application.StatusBar = False
End Sub


Ben

 

[Martin Leese]

GS wrote:
....

....
That's not available to VBA. How you get a count of selected cells in
VBA is by using 'Selection'...

Thanks to GS and Ben. However, I was really
looking to activate the "NUM" thingie.

 

[GS]

Ok, I looked further into this and found a way to access the items on
the right-click popup. This commandbar's name is "AutoCalculate". Each
menuitem (control) can be accessed as follows...

CommandBars("AutoCalculate").Controls("&None").Execute
OR
CommandBars("AutoCalculate").Controls(1).Execute

...where you can specify the menuitem by its Caption or its Index as
listed below.

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...

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

...so you can use it in code as follows...

CommandBars("AutoCalculate").Controls(AutoCalc.Average).Execute

...so it works with AutoSense while typing in a code window.

--
Garry

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

 

[isabelle]

hi,

Application.CommandBars("Auto Sum").FindControl(ID:=226).Execute

' see if it exists and Id number
Dim cbr As CommandBar
Set cbr = Application.CommandBars("Auto Sum")
For Each cbt In cbr.Controls
Debug.Print cbt.ID, cbt.Caption
Next

isabelle

Le 2013-03-21 10:44, Martin Leese a écrit :

 

[GS]

CommandBars("Auto Sum") did not appear until XL2003. It invokes 'Insert
Formula' on the active cell. This is different than turning on the
AutoCalcs that display on the right side of the status bar, which is
what I think the OP wants to do!

--
Garry

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

 

[Claus Busch]

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

 

[GS]

Claus,
Very nice implementation! I'd be inclined, though, to maybe take it a
step further and fully 'hook' the controls on that cbar to run my
procedure.

--
Garry

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

 

[GS]

I did play around with this a bit to see how hooking this cbar might be
best implemented for practical use. I have it working for contiguous
selections where the active cell receives the results. Not sure yet how
useful this could be but here's my code...

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

 

[Martin Leese]

Ok, I looked further into this and found a way to access the items on
the right-click popup. This commandbar's name is "AutoCalculate". Each
menuitem (control) can be accessed as follows...

CommandBars("AutoCalculate").Controls("&None").Execute

Application.CommandBars("AutoCalculate").Controls("&Count").Execute

does exactly what I want.