In the bottom right hand corner of the status bar (right click). A user has
the ability to either Average, Count, Count Nums, Max, Min or Sum the
contents of the highlighted range. I want to be able to call that procedure
using an OnKey Method to copy that value to the clipboard. Then the user
could paste that value elsewhere in excel.

Squid,

I don't believe there is any way to retrieve that value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Squid" wrote in message
news:LzLXb.41526$uV3.71986@attbi_s51...
In the bottom right hand corner of the status bar (right
click). A user has
the ability to either Average, Count, Count Nums, Max, Min or
Sum the
contents of the highlighted range. I want to be able to call
that procedure
using an OnKey Method to copy that value to the clipboard.
Then the user
could paste that value elsewhere in excel.

msgbox application.Sum(selection)

application.CountA(selection)

selection.Count

Application.Average(selection)

and so forth.

--
Regards,
Tom Ogilvy


"Chip Pearson" wrote in message
...
Squid,

I don't believe there is any way to retrieve that value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Squid" wrote in message
news:LzLXb.41526$uV3.71986@attbi_s51...
In the bottom right hand corner of the status bar (right
click). A user has
the ability to either Average, Count, Count Nums, Max, Min or
Sum the
contents of the highlighted range. I want to be able to call
that procedure
using an OnKey Method to copy that value to the clipboard.
Then the user
could paste that value elsewhere in excel.

The following will call the menu if it's of any use:
Application.CommandBars(49).ShowPopup

Regards,
Greg

-----Original Message-----
In the bottom right hand corner of the status bar (right
click). A user has
the ability to either Average, Count, Count Nums, Max,
Min or Sum the
contents of the highlighted range. I want to be able to
call that procedure
using an OnKey Method to copy that value to the
clipboard. Then the user
could paste that value elsewhere in excel.


.

That threw an error in Excel 97.

--
Regards,
Tom Ogilvy


"Greg Wilson" wrote in message
...
The following will call the menu if it's of any use:
Application.CommandBars(49).ShowPopup

Regards,
Greg

-----Original Message-----
In the bottom right hand corner of the status bar (right
click). A user has
the ability to either Average, Count, Count Nums, Max,
Min or Sum the
contents of the highlighted range. I want to be able to
call that procedure
using an OnKey Method to copy that value to the
clipboard. Then the user
could paste that value elsewhere in excel.


.

Substituting "AutoCalculate" for the number 49 should fix
it (I don't have access to xl97 so can't check). I
thought Squid might want to experiment with something like
the following in addition to his stated goal:

(In ThisWorkbook module)
Private Sub Workbook_Open()
Dim Ctrl As CommandBarControl
Set Ctrl = Application.CommandBars _
("AutoCalculate").Controls.Add(msoControlButton ,
Temporary:=True)
Ctrl.OnAction = "TestYYY"
Ctrl.Caption = "StdDev"
End Sub

(In standard module)
Sub TestXXX()
With Application
.StatusBar = ""
.DisplayStatusBar = True
.CommandBars("AutoCalculate").ShowPopup
End With
End Sub

Sub TestYYY()
Dim Answer As String
On Error Resume Next
Answer = Format(WorksheetFunction.StDev(Selection), "#.00")
Application.StatusBar = "StdDev = " & Answer
On Error GoTo 0
End Sub

Regards,
Greg


-----Original Message-----
That threw an error in Excel 97.

--
Regards,
Tom Ogilvy


"Greg Wilson" wrote
in message
...
The following will call the menu if it's of any use:
Application.CommandBars(49).ShowPopup

Regards,
Greg

-----Original Message-----
In the bottom right hand corner of the status bar
(right
click). A user has
the ability to either Average, Count, Count Nums, Max,
Min or Sum the
contents of the highlighted range. I want to be able
to
call that procedure
using an OnKey Method to copy that value to the
clipboard. Then the user
could paste that value elsewhere in excel.


.



.

I should have mentioned that "TestXXX" would be the
procedure called by the user (by a command button?)

Regards,
Greg

-----Original Message-----
Substituting "AutoCalculate" for the number 49 should fix
it (I don't have access to xl97 so can't check). I
thought Squid might want to experiment with something
like
the following in addition to his stated goal:

(In ThisWorkbook module)
Private Sub Workbook_Open()
Dim Ctrl As CommandBarControl
Set Ctrl = Application.CommandBars _
("AutoCalculate").Controls.Add(msoControlButton ,
Temporary:=True)
Ctrl.OnAction = "TestYYY"
Ctrl.Caption = "StdDev"
End Sub

(In standard module)
Sub TestXXX()
With Application
.StatusBar = ""
.DisplayStatusBar = True
.CommandBars("AutoCalculate").ShowPopup
End With
End Sub

Sub TestYYY()
Dim Answer As String
On Error Resume Next
Answer = Format(WorksheetFunction.StDev
(Selection), "#.00")
Application.StatusBar = "StdDev = " & Answer
On Error GoTo 0
End Sub

Regards,
Greg


-----Original Message-----
That threw an error in Excel 97.

--
Regards,
Tom Ogilvy


"Greg Wilson"
wrote
in message
.. .
The following will call the menu if it's of any use:
Application.CommandBars(49).ShowPopup

Regards,
Greg

-----Original Message-----
In the bottom right hand corner of the status bar
(right
click). A user has
the ability to either Average, Count, Count Nums,
Max,
Min or Sum the
contents of the highlighted range. I want to be able
to
call that procedure
using an OnKey Method to copy that value to the
clipboard. Then the user
could paste that value elsewhere in excel.


.



.

.

In XP, the index is 44.
--

Vasant


"Greg Wilson" wrote in message
...
Substituting "AutoCalculate" for the number 49 should fix
it (I don't have access to xl97 so can't check). I
thought Squid might want to experiment with something like
the following in addition to his stated goal:

(In ThisWorkbook module)
Private Sub Workbook_Open()
Dim Ctrl As CommandBarControl
Set Ctrl = Application.CommandBars _
("AutoCalculate").Controls.Add(msoControlButton ,
Temporary:=True)
Ctrl.OnAction = "TestYYY"
Ctrl.Caption = "StdDev"
End Sub

(In standard module)
Sub TestXXX()
With Application
.StatusBar = ""
.DisplayStatusBar = True
.CommandBars("AutoCalculate").ShowPopup
End With
End Sub

Sub TestYYY()
Dim Answer As String
On Error Resume Next
Answer = Format(WorksheetFunction.StDev(Selection), "#.00")
Application.StatusBar = "StdDev = " & Answer
On Error GoTo 0
End Sub

Regards,
Greg


-----Original Message-----
That threw an error in Excel 97.

--
Regards,
Tom Ogilvy


"Greg Wilson" wrote
in message
...
The following will call the menu if it's of any use:
Application.CommandBars(49).ShowPopup

Regards,
Greg

-----Original Message-----
In the bottom right hand corner of the status bar
(right
click). A user has
the ability to either Average, Count, Count Nums, Max,
Min or Sum the
contents of the highlighted range. I want to be able
to
call that procedure
using an OnKey Method to copy that value to the
clipboard. Then the user
could paste that value elsewhere in excel.


.



.