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

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


<[email protected]> wrote in message news:[email protected]...
> 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
>

Hi Aaron,

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


---
Regards,
Norman



<[email protected]> wrote in message
news:[email protected]...
> 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
>

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)


<[email protected]> wrote in message
news:[email protected]...
> 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
>

Hi Ron,

What does Aantal mean?

Regards

Bob


"Ron de Bruin" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
> <[email protected]> wrote in message
news:[email protected]...
> > 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
> >
>
>

Hi Bob

Dutch word for amount

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


"Bob Phillips" <[email protected]> wrote in message news:%[email protected]...
> Hi Ron,
>
> What does Aantal mean?
>
> Regards
>
> Bob
>
>
> "Ron de Bruin" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>> <[email protected]> wrote in message
> news:[email protected]...
>> > 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
>> >
>>
>>
>
>

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=ADl Target As Range)
> Application.StatusBar =3D 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)
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

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


<[email protected]> wrote in message
news:[email protected]...
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)
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

Thank you!