what is this object called? - right hand corner of statusbar

S

Squid

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

Chip Pearson

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
 
T

Tom Ogilvy

msgbox application.Sum(selection)

application.CountA(selection)

selection.Count

Application.Average(selection)

and so forth.
 
G

Greg Wilson

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

Regards,
Greg
 
G

Greg Wilson

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
 
G

Greg Wilson

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

Regards,
Greg
 
V

Vasant Nanavati

In XP, the index is 44.
--

Vasant


Greg Wilson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top