to Bob Phillips

[Jack Sons]

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

 

[Guest]

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]

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)

 

[Jack Sons]

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.

 

[Jack Sons]

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.

 

[Guest]

Try

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

HTH,
Barb Reinhardt

 

[Bob Phillips]

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)

 

[Ragdyer]

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

 

[Jack Sons]

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]

Might give that a try! Thanks.

 

[Bob Phillips]

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]

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]

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]

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]

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]

Bob,

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

Jack.