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