Adding a new function to values in the status bar

[aaronfude]

Hi,

That nice feature of excel where the status bar displays either the sum
or the average or the count of the selection - can that be extended
with another function, such as geometric average or median?


Any solution is welcome, but .net is preferred!

Thank you!

Aaron Fude

 

[Ron de Bruin]

Hi

You can use this event in the Thisworkbook module

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo ErrHandler:
With Application
.StatusBar = "Aantal: " & .WorksheetFunction.Count(Target)
End With
Exit Sub
ErrHandler:
Application.StatusBar = False
End Sub

 

[Norman Jones]

Hi Aaron,

I do not believe that the display to which you refer, is exposed to
programmatic access.

 

[Bob Phillips]

I don't think that it is possible.

Here is a suggestion by John Walkenbach, VBA though

I'm pretty sure that it's not possible. Nothing in the Excel object model
deals with that feature.


However, you can monitor the SelectionChange event, then display some info
in the left part of the status bar. Here's a simple Sub that demonstrates
(using SUM):


Private Sub Worksheet_SelectionChange(ByVa­l Target As Range)
Application.StatusBar = Application.Sum(Target)
End Sub


Note that this will overwrite the information that normally appears there.



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Hi Ron,

What does Aantal mean?

Regards

Bob

Hi

You can use this event in the Thisworkbook module

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo ErrHandler:
With Application
.StatusBar = "Aantal: " & .WorksheetFunction.Count(Target)
End With
Exit Sub
ErrHandler:
Application.StatusBar = False
End Sub

 

[Ron de Bruin]

Hi Bob

Dutch word for amount

 

[aaronfude]

Hi,

Thanks for the responses.

Can the solution proposed here be made available as an VBA add-in or
must this code be added to the VBA associated directly with the sheet?

As a sumwhat unrelated question, rather than writing to a toolbar, can
you write to a field in a custom toolbar?

Many thanks again,

Aaron Fude

 

[Bob Phillips]

You can do it as application events, which will work equally well in an
add-in.

Dim WithEvents app As Application

Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
Application.StatusBar = Application.Sum(Target)
End Sub

Private Sub Workbook_Open()
Set app = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

In a tolbar button, it looke like this

Private WithEvents app As Application
Private oCtl As CommandBarControl

Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
oCtl.Caption = "SUM = " & Application.Sum(Target)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
oCtl.Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Set app = Application
On Error Resume Next
oCtl.Delete
On Error GoTo 0
With Application.CommandBars("Standard")
Set oCtl = .Controls.Add(temporary:=True)
oCtl.BeginGroup = True
oCtl.Caption = "<<SUM>>"
oCtl.Style = msoButtonCaption
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


Hi,

Thanks for the responses.

Can the solution proposed here be made available as an VBA add-in or
must this code be added to the VBA associated directly with the sheet?

As a sumwhat unrelated question, rather than writing to a toolbar, can
you write to a field in a custom toolbar?

Many thanks again,

Aaron Fude

 

[aaronfude]

Thank you!