Bob,

In the thread "Multiple Sum Functions on status bar" I asked the question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Toppers,

Thanks for your help, but I did exactly the as you said to no avail (numbers
in A1:A4, highlighted, but not sum and count at the same time in the status
bar).

When I look at the essentials of the code (see below) I see that Average
will give the average of the selection, that Sum will give the total of the
selection etc. But I don't see any clue that tells me that I can select a
certain item on the menue (that I get when I right click on the status bar)
that wil cause the statusbar to show count and sum at the same time. I can
check in the menue the sum item or the count item, but not both, so it seems
quite logical that only one of them will show up.

Or do I something wrong or do I think along a wrong line of thought?

Oh my, oh my, suddenly I see how stupid I am.
I was looking (was totally focused) to the right side of the status bar
where normally the sum, count etc. will appear (in the box that is reserved
for it) and did not notice that all data (average up to min) appear as a
kind of message at the left side of the status bar.

A thousand times sorry!

Jack.

------------------------------------------------------------------------------------------------
Application.StatusBar =
"Average=" & Application.Average(Selection) & _"; " & _
"Count=" & Application.CountA(Selection) & "; " & _
"Count nums=" & Application.Count(Selection) & _"; " & _
"Sum=" & Application.Sum(Selection) & "; " & _
"Max=" & Application.Max(Selection) & "; " & _
"Min=" & Application.Min(Selection)





"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or count
in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Toppers,

This useful code will work only in the sheet where I put it in the sheet
code module. Is it possible to put it somehow in personal.xls so that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or count
in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the sheet
code module. Is it possible to put it somehow in personal.xls so that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Try

If Selection.count = 0 then application.statusbar = false

HTH,
Barb Reinhardt

"Jack Sons" wrote:

And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the sheet
code module. Is it possible to put it somehow in personal.xls so that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Jack,

Sorry to miss the last post, I use a couple of machines and I must have
posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the sheet
code module. Is it possible to put it somehow in personal.xls so that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

I do the same Bob (use many machines), and keep track by changing the caps
placement in my name.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bob Phillips" wrote in message
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must have
posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the
sheet
code module. Is it possible to put it somehow in personal.xls so that
it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click
on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack

-----------------------------------------------------------------------
--------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Bob,

In my this workbook module exists the following code:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

I forgot why I put it there long ago, but I think to remember that I got
troubles when I removed it.
Can the existing "Private Sub Workbook_Open()" be combined with the new one?

Can you help me out?

By the way, does your line of code with "Application.StatusBar = False"
clear the status bar of the remaining results from the last time a range was
highlighted or does it only prevent it from being over written by the
results of highlighting only one cell (or selecting a sheet with no
highlighted cell)?

Jack.


"Bob Phillips" schreef in bericht
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must have
posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the sheet
code module. Is it possible to put it somehow in personal.xls so that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Might give that a try! Thanks.

"Ragdyer" wrote in message
...
I do the same Bob (use many machines), and keep track by changing the caps
placement in my name.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Jack,

Your Workbook_Open event already primes the application events, so there is
no need to add another. What you do need to do though is change the
application event from

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

to

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

then it should work okay (as long as you don't already have a
xlApp_SheetSelectionChange event).

Application.Statusbar returns or sets the text in the status bar, setting it
to False restores the default status. It does clear out anything already
there.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Bob,

In my this workbook module exists the following code:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

I forgot why I put it there long ago, but I think to remember that I got
troubles when I removed it.
Can the existing "Private Sub Workbook_Open()" be combined with the new
one?

Can you help me out?

By the way, does your line of code with "Application.StatusBar = False"
clear the status bar of the remaining results from the last time a range
was highlighted or does it only prevent it from being over written by the
results of highlighting only one cell (or selecting a sheet with no
highlighted cell)?

Jack.


"Bob Phillips" schreef in bericht
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must have
posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the
sheet
code module. Is it possible to put it somehow in personal.xls so that
it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click
on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at the
same
time. I put your code in a sheet module, a normal module and the this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Bob,

I now have the code in the "my workbook" module of personal.xls that you see
below.
I can't close Excel because the second line of your code ("Public
WithEvents App As Application") is not accepted (the error message says that
something in sub or function is not valid). Besides that, the code doesn't
work.

I'm sure only something small is incorrect, but I don't know what and
certainly not how to correct it.

Please help once more.

Jack.

---------------------------------------------------------------------------------------------------------
Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Option Explicit

Public WithEvents App As Application

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub
------------------------------------------------------------------------------------------------------
"Bob Phillips" schreef in bericht
...
Jack,

Your Workbook_Open event already primes the application events, so there
is no need to add another. What you do need to do though is change the
application event from

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

to

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

then it should work okay (as long as you don't already have a
xlApp_SheetSelectionChange event).

Application.Statusbar returns or sets the text in the status bar, setting
it to False restores the default status. It does clear out anything
already there.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Bob,

In my this workbook module exists the following code:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

I forgot why I put it there long ago, but I think to remember that I got
troubles when I removed it.
Can the existing "Private Sub Workbook_Open()" be combined with the new
one?

Can you help me out?

By the way, does your line of code with "Application.StatusBar = False"
clear the status bar of the remaining results from the last time a range
was highlighted or does it only prevent it from being over written by the
results of highlighting only one cell (or selecting a sheet with no
highlighted cell)?

Jack.


"Bob Phillips" schreef in bericht
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must have
posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the
sheet
code module. Is it possible to put it somehow in personal.xls so that
it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click
on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at
the same
time. I put your code in a sheet module, a normal module and the
this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Jack,

You have clearly put it in the wrong code module. It should be in
ThisWorkbook, I have no idea what "my workbook" is.

Hear are some directions to get to this module

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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jack Sons" wrote in message
...
Bob,

I now have the code in the "my workbook" module of personal.xls that you
see below.
I can't close Excel because the second line of your code ("Public
WithEvents App As Application") is not accepted (the error message says
that something in sub or function is not valid). Besides that, the code
doesn't work.

I'm sure only something small is incorrect, but I don't know what and
certainly not how to correct it.

Please help once more.

Jack.

---------------------------------------------------------------------------------------------------------
Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Option Explicit

Public WithEvents App As Application

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub
------------------------------------------------------------------------------------------------------
"Bob Phillips" schreef in bericht
...
Jack,

Your Workbook_Open event already primes the application events, so there
is no need to add another. What you do need to do though is change the
application event from

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

to

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

then it should work okay (as long as you don't already have a
xlApp_SheetSelectionChange event).

Application.Statusbar returns or sets the text in the status bar, setting
it to False restores the default status. It does clear out anything
already there.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Bob,

In my this workbook module exists the following code:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

I forgot why I put it there long ago, but I think to remember that I got
troubles when I removed it.
Can the existing "Private Sub Workbook_Open()" be combined with the new
one?

Can you help me out?

By the way, does your line of code with "Application.StatusBar = False"
clear the status bar of the remaining results from the last time a range
was highlighted or does it only prevent it from being over written by
the results of highlighting only one cell (or selecting a sheet with no
highlighted cell)?

Jack.


"Bob Phillips" schreef in bericht
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must have
posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the
sheet
code module. Is it possible to put it somehow in personal.xls so that
it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code - right-click
on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at
the same
time. I put your code in a sheet module, a normal module and the
this
workbook module, but neither gave any result. I still get or sum or
count in
the status bar, but not both at the same time. What did I do wrong?

Jack Sons
The Netherlands

Bob,

I'm sorry, I just frased it wrong (matter of foreign language), I really
meant the "ThisWorkbook" module of personal.xls. I checked it.
So I must be very close but I don't see what's wrong. Perhaps the two
slightly different lines of code with "Public WithEvents ..."?

Jack.

Jack.

"Bob Phillips" schreef in bericht
...
Jack,

You have clearly put it in the wrong code module. It should be in
ThisWorkbook, I have no idea what "my workbook" is.

Hear are some directions to get to this module

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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jack Sons" wrote in message
...
Bob,

I now have the code in the "my workbook" module of personal.xls that you
see below.
I can't close Excel because the second line of your code ("Public
WithEvents App As Application") is not accepted (the error message says
that something in sub or function is not valid). Besides that, the code
doesn't work.

I'm sure only something small is incorrect, but I don't know what and
certainly not how to correct it.

Please help once more.

Jack.

---------------------------------------------------------------------------------------------------------
Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Option Explicit

Public WithEvents App As Application

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub
------------------------------------------------------------------------------------------------------
"Bob Phillips" schreef in bericht
...
Jack,

Your Workbook_Open event already primes the application events, so there
is no need to add another. What you do need to do though is change the
application event from

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)

to

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

then it should work okay (as long as you don't already have a
xlApp_SheetSelectionChange event).

Application.Statusbar returns or sets the text in the status bar,
setting it to False restores the default status. It does clear out
anything already there.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Bob,

In my this workbook module exists the following code:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

I forgot why I put it there long ago, but I think to remember that I
got troubles when I removed it.
Can the existing "Private Sub Workbook_Open()" be combined with the new
one?

Can you help me out?

By the way, does your line of code with "Application.StatusBar = False"
clear the status bar of the remaining results from the last time a
range was highlighted or does it only prevent it from being over
written by the results of highlighting only one cell (or selecting a
sheet with no highlighted cell)?

Jack.


"Bob Phillips" schreef in bericht
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must
have posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the
sheet
code module. Is it possible to put it somehow in personal.xls so
that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code -
right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status bar
displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at
the same
time. I put your code in a sheet module, a normal module and the
this
workbook module, but neither gave any result. I still get or sum
or count in
the status bar, but not both at the same time. What did I do
wrong?

Jack Sons
The Netherlands

Hang on. You already have a WthEvents line, so you don't need mine.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack Sons" wrote in message
...
Bob,

I'm sorry, I just frased it wrong (matter of foreign language), I really
meant the "ThisWorkbook" module of personal.xls. I checked it.
So I must be very close but I don't see what's wrong. Perhaps the two
slightly different lines of code with "Public WithEvents ..."?

Jack.

Jack.

"Bob Phillips" schreef in bericht
...
Jack,

You have clearly put it in the wrong code module. It should be in
ThisWorkbook, I have no idea what "my workbook" is.

Hear are some directions to get to this module

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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jack Sons" wrote in message
...
Bob,

I now have the code in the "my workbook" module of personal.xls that you
see below.
I can't close Excel because the second line of your code ("Public
WithEvents App As Application") is not accepted (the error message says
that something in sub or function is not valid). Besides that, the code
doesn't work.

I'm sure only something small is incorrect, but I don't know what and
certainly not how to correct it.

Please help once more.

Jack.

---------------------------------------------------------------------------------------------------------
Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Option Explicit

Public WithEvents App As Application

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub
------------------------------------------------------------------------------------------------------
"Bob Phillips" schreef in bericht
...
Jack,

Your Workbook_Open event already primes the application events, so
there is no need to add another. What you do need to do though is
change the application event from

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

to

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

then it should work okay (as long as you don't already have a
xlApp_SheetSelectionChange event).

Application.Statusbar returns or sets the text in the status bar,
setting it to False restores the default status. It does clear out
anything already there.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Bob,

In my this workbook module exists the following code:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

I forgot why I put it there long ago, but I think to remember that I
got troubles when I removed it.
Can the existing "Private Sub Workbook_Open()" be combined with the
new one?

Can you help me out?

By the way, does your line of code with "Application.StatusBar =
False" clear the status bar of the remaining results from the last
time a range was highlighted or does it only prevent it from being
over written by the results of highlighting only one cell (or
selecting a sheet with no highlighted cell)?

Jack.


"Bob Phillips" schreef in bericht
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must
have posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the
sheet
code module. Is it possible to put it somehow in personal.xls so
that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code -
right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status
bar displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum at
the same
time. I put your code in a sheet module, a normal module and the
this
workbook module, but neither gave any result. I still get or sum
or count in
the status bar, but not both at the same time. What did I do
wrong?

Jack Sons
The Netherlands

Bob,

Now it works perfectly.
Thank you so much for your help and perhaps above all, for your patience.

Jack.


"Bob Phillips" schreef in bericht
...
Hang on. You already have a WthEvents line, so you don't need mine.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Bob,

I'm sorry, I just frased it wrong (matter of foreign language), I really
meant the "ThisWorkbook" module of personal.xls. I checked it.
So I must be very close but I don't see what's wrong. Perhaps the two
slightly different lines of code with "Public WithEvents ..."?

Jack.

Jack.

"Bob Phillips" schreef in bericht
...
Jack,

You have clearly put it in the wrong code module. It should be in
ThisWorkbook, I have no idea what "my workbook" is.

Hear are some directions to get to this module

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


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Jack Sons" wrote in message
...
Bob,

I now have the code in the "my workbook" module of personal.xls that
you see below.
I can't close Excel because the second line of your code ("Public
WithEvents App As Application") is not accepted (the error message says
that something in sub or function is not valid). Besides that, the code
doesn't work.

I'm sure only something small is incorrect, but I don't know what and
certainly not how to correct it.

Please help once more.

Jack.

---------------------------------------------------------------------------------------------------------
Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

Option Explicit

Public WithEvents App As Application

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub
------------------------------------------------------------------------------------------------------
"Bob Phillips" schreef in bericht
...
Jack,

Your Workbook_Open event already primes the application events, so
there is no need to add another. What you do need to do though is
change the application event from

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

to

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

then it should work okay (as long as you don't already have a
xlApp_SheetSelectionChange event).

Application.Statusbar returns or sets the text in the status bar,
setting it to False restores the default status. It does clear out
anything already there.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Jack Sons" wrote in message
...
Bob,

In my this workbook module exists the following code:

Option Explicit
Public WithEvents xlApp As Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set xlApp = Nothing
End Sub
Private Sub Workbook_Open()
Set xlApp = Application
End Sub

I forgot why I put it there long ago, but I think to remember that I
got troubles when I removed it.
Can the existing "Private Sub Workbook_Open()" be combined with the
new one?

Can you help me out?

By the way, does your line of code with "Application.StatusBar =
False" clear the status bar of the remaining results from the last
time a range was highlighted or does it only prevent it from being
over written by the results of highlighting only one cell (or
selecting a sheet with no highlighted cell)?

Jack.


"Bob Phillips" schreef in bericht
...
Jack,

Sorry to miss the last post, I use a couple of machines and I must
have posted that elsewhere.

Anyway, put this in the ThisWorkbook module of your Personal.xls

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

Application.DisplayStatusBar = True
On Error Resume Next
If Target.Count < 2 Then
Application.StatusBar = False
Else
Application.StatusBar = _
"Average=" & Application.Average(Target) & _
"; " & _
"Count=" & Application.CountA(Target) & "; " & _
"Count nums=" & Application.Count(Target) & _
"; " & _
"Sum=" & Application.Sum(Target) & "; " & _
"Max=" & Application.Max(Target) & "; " & _
"Min=" & Application.Min(Target)
End If

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my addy)

"Jack Sons" wrote in message
...
And I would like to add a line of code that says

IF(number of highlighted cells in Range = 0 or 1, clear status bar)

TIA

Jack.


"Jack Sons" schreef in bericht
...
Toppers,

This useful code will work only in the sheet where I put it in the
sheet
code module. Is it possible to put it somehow in personal.xls so
that it
will work in every sheet of every workbook that I open?

Jack.

"Toppers" schreef in bericht
...
Jack,
FYI: I tried Bob's code and it worked fine.

I had data in A1:A4 on Sheet1 (where I placed the code -
right-click on
Sheet1 tab, "View code", copy/paste),. hit enter and the status
bar displayed
the various functions as per Bob's code.

"Jack Sons" wrote:

Bob,

In the thread "Multiple Sum Functions on status bar" I asked the
question
below. Can you tell me what to do?

Jack
-------------------------------------------------------------------------------------------------------------
Bob,

I really would like to have the status bar shouw count and sum
at the same
time. I put your code in a sheet module, a normal module and the
this
workbook module, but neither gave any result. I still get or sum
or count in
the status bar, but not both at the same time. What did I do
wrong?

Jack Sons
The Netherlands