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.

--
Many thanks,
Martin Leese
E-mail: LID
Web: http://members.tripod.com/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.

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

GS wrote:
....
How do I activate the "NUM" thingie and
select "COUNT" using VBA?
....
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.

--
Regards,
Martin Leese
E-mail: LID
Web: http://members.tripod.com/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").Exe cute
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.Ave rage).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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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 wrote:
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").Exe cute

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

does exactly what I want.

--
Many thanks,
Martin Leese
E-mail: LID
Web: http://members.tripod.com/martin_leese/

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 wrote:
...
How do I activate the "NUM" thingie and
select "COUNT" using VBA?
...
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.

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

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 could add 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