Adding a new function to values in the status bar
 

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

Adding a new function to values in the status bar
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
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

Adding a new function to values in the status bar
 

Hi Aaron,

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


---
Regards,
Norman



wrote in message
oups.com...
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

Adding a new function to values in the status bar
 

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)


wrote in message
oups.com...
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

Adding a new function to values in the status bar
 

Hi Ron,

What does Aantal mean?

Regards

Bob


"Ron de Bruin" wrote in message
...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message
oups.com...
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

Adding a new function to values in the status bar
 

Hi Bob

Dutch word for amount

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Hi Ron,

What does Aantal mean?

Regards

Bob


"Ron de Bruin" wrote in message
...
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message
oups.com...
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

Adding a new function to values in the status bar
 

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 wrote:
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)


wrote in message
oups.com...
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

Adding a new function to values in the status bar
 

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


wrote in message
oups.com...
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 wrote:
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)


wrote in message
oups.com...
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

Adding a new function to values in the status bar
 

Thank you!