Solved: multiply statusbar

[danovkos]

Hi all,
pls. i found one very old code from net, but it works only for concrete workbook if i copy this code to ThisWorkbook module.

Is it possible to change this code this way, that then will be possible it use in Personal.xls.

What i want is always, when i run excel, my status bar will show all this info (count, sum, max, min of range cells)

thank you

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Dim statusLine As String
 
  On Error Resume Next
  With Application.WorksheetFunction
    statusLine = "Sum=" & .Sum(Target)
    statusLine = statusLine & "; Average=" & .Average(Target)
    statusLine = statusLine & "; Non-blank=" & .CountA(Target)
    statusLine = statusLine & "; Min=" & .Min(Target)
    statusLine = statusLine & "; Max=" & .Max(Target)
  End With
 
  Application.StatusBar = statusLine
End Sub
 
Private Sub Workbook_Deactivate()
  Application.StatusBar = False
End Sub

[Bob Phillips]

[vba]

Option Explicit

Private WithEvents App As Application

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

On Error Resume Next
With Application.WorksheetFunction
statusLine = "Sum=" & .Sum(Target)
statusLine = statusLine & "; Average=" & .Average(Target)
statusLine = statusLine & "; Non-blank=" & .CountA(Target)
statusLine = statusLine & "; Min=" & .Min(Target)
statusLine = statusLine & "; Max=" & .Max(Target)
End With

Application.StatusBar = statusLine

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub
[/vba]

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

[danovkos]

thx, but this it the same insnt it?
this works also only for concrete workbook. But i looking for something, what will works for all my excel files - it will be applied in personal or i dont know...
is something like i want possible...i hoped, that this macro only transform to other form for personal.xls use

[anandbohra]

thx, but this it the same insnt it?
this works also only for concrete workbook. But i looking for something, what will works for all my excel files - it will be applied in personal or i dont know...
is something like i want possible...i hoped, that this macro only transform to other form for personal.xls use

Yes u have to put this code in personal.xls file (which by default start with excel) I tried XLD code & its working fine on my Excel (every opened workbook)

one question to XLD
is there is any double click event on statusbar?

my plan is to use this same as per normal procedure where we right click & select sum or average (one at a time)
I want to know that is there is any method which add your code (XLD's code) into the right click menu & select all

or double click on statusbar to toggle the code (activate / deactivate)

[danovkos]

yes, now it works,
it start works after restart excel

my last question is, how can i set the format of displaying amounts?
i tried this, but it doesnt help.

statusLine = statusLine & ";   Average=" & .Average(Target)& Format(number,"#,##0")

thx a lot

[anandbohra]

Try this

[VBA] statusLine = statusLine & "; Average=" & Format(.Average(Target), "##,##0.00")
[/VBA]

and various other formats (VBA taken from excel help page)

Format Function Example@import url(office.css);MyTime and MyDate are displayed in the development environment using current system short time setting and short date setting.
Dim MyTime, MyDate, MyStr MyTime = #17:04:23# MyDate = #January 27, 1993# ' Returns current system time in the system-defined long time format. MyStr = Format(Time, "Long Time") ' Returns current system date in the system-defined long date format. MyStr = Format(Date, "Long Date") MyStr = Format(MyTime, "h:m:s") ' Returns "17:4:23". MyStr = Format(MyTime, "hh:mm:ss AMPM") ' Returns "05:04:23 PM". MyStr = Format(MyDate, "dddd, mmm d yyyy") ' Returns "Wednesday, ' Jan 27 1993". ' If format is not supplied, a string is returned. MyStr = Format(23) ' Returns "23". ' User-defined formats. MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40". MyStr = Format(334.9, "###0.00") ' Returns "334.90". MyStr = Format(5, "0.00%") ' Returns "500.00%". MyStr = Format("HELLO", "<") ' Returns "hello". MyStr = Format("This is it", ">") ' Returns "THIS IS IT".

[danovkos]

PERFECT
THANK YOU VERY MUCH

[Bob Phillips]

Yes u have to put this code in personal.xls file (which by default start with excel) I tried XLD code & its working fine on my Excel (every opened workbook)

one question to XLD
is there is any double click event on statusbar?

my plan is to use this same as per normal procedure where we right click & select sum or average (one at a time)
I want to know that is there is any method which add your code (XLD's code) into the right click menu & select all

or double click on statusbar to toggle the code (activate / deactivate)

Why bother, Excel already has that builtin.

[anandbohra]

I know Excle has builtin but I want to add your code module in that menu to start / stop your code

now when one right click it gives menu with these item
none
Average
Count
Count Nums
Max
Min
Sum

I want a code or somethings which add one more option there to turn on or off your code
or property through which i double click status bar & turn your code on or off

THIS IS JUST MY IMAGINATION PL DO LET ME KNOW IT IS POSSIBLE OR NOT

[Bob Phillips]

You cannot add an item to the statusbar that does that, at least not without a lot of effort, as that is not a commandbar, but you could add to the cell right-click menu, or maybe the sheets right-click menu. Does that work for you?

[danovkos]

guys one more thing
the count ant otheer functions count and sum also hiden cells
f.e. if i filtered data and select a range it return count of all cell (hidden too)...how can i count only displayed cell?

[anandbohra]

but you could add to the cell right-click menu, or maybe the sheets right-click menu. Does that work for you?

Sheet right click menu will do

say I right click on any sheet & click show details (to show your code output in statusbar)
& when not required hide details (to make statusbar = false)

[anandbohra]

f.e. if i filtered data and select a range it return count of all cell (hidden too)...how can i count only displayed cell?

try this
[VBA] statusLine = statusLine & "; Vis =" & .CountA(Target.SpecialCells(xlCellTypeVisible))
[/VBA]

[Bob Phillips]

Sheet right click menu will do

say I right click on any sheet & click show details (to show your code output in statusbar)
& when not required hide details (to make statusbar = false)

Add this to ThisWorkbook

[vba]

Option Explicit

Private WithEvents App As Application

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

If SBCtl.State = msoButtonDown Then

ShowSBInfo
Else

Application.StatusBar = False
End If

End Sub

Private Sub Workbook_Open()
Set App = Application

With Application.CommandBars("Cell")

On Error Resume Next
.Controls("Statusbar info").Delete
On Error GoTo 0

Set SBCtl = .Controls.Add(Type:=msoControlButton, temporary:=True)

With SBCtl

.BeginGroup = True
.Caption = "Statusbar info"
.State = msoButtonUp
.OnAction = "ToggleStatusbar"
End With

Call sbinfo
End With
End Sub
[/vba]

and this to a standard code module

[vba]


Global SBCtl As CommandBarControl

Public Function ToggleStatusbar()
If SBCtl.State = msoButtonDown Then

SBCtl.State = msoButtonUp
Application.StatusBar = False
Else

SBCtl.State = msoButtonDown
ShowSBInfo
End If
End Function

Public Function ShowSBInfo()
Dim statusLine As String

On Error Resume Next
With Application.WorksheetFunction
statusLine = "Sum=" & .Sum(Selection)
statusLine = statusLine & "; Average=" & .Average(Selection)
statusLine = statusLine & "; Non-blank=" & .CountA(Selection)
statusLine = statusLine & "; Min=" & .Min(Selection)
statusLine = statusLine & "; Max=" & .Max(Selection)
End With

Application.StatusBar = statusLine
End Function
[/vba]

[danovkos]

yes, great
works perfect
thx

[Bob Phillips]

guys one more thing
the count ant otheer functions count and sum also hiden cells
f.e. if i filtered data and select a range it return count of all cell (hidden too)...how can i count only displayed cell?

This needs reselecting after each filter/unfilter

[vba]

Option Explicit

Private WithEvents App As Application

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

On Error Resume Next
With Application.WorksheetFunction
statusLine = "Sum=" & .Subtotal(109, Target)
statusLine = statusLine & "; Average=" & Round(.Subtotal(101, Target), 1)
statusLine = statusLine & "; Non-blank=" & .Subtotal(103, Target)
statusLine = statusLine & "; Min=" & .Subtotal(105, Target)
statusLine = statusLine & "; Max=" & .Subtotal(104, Target)
End With

Application.StatusBar = statusLine

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub
[/vba]

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

[danovkos]

sorry one question
now, with this little change of code it show always sum all of cells in sheet if is nothing selected, is it ok?
i think i am with cursor out of table and it instead display nothing, it count for each function whole data in sheet

[anandbohra]

Hi XLD
tried your code but it is not working

one bug found & resolved
as on workbook open event u mention
[VBA]call Sbinfo[/VBA]
which actualy was

[VBA] Call ShowSBInfo[/VBA]


but then also on click it reset the statusbar but not showing the result based
on selection changes

[danovkos]

now i figured out, that the code with "visible" function doesnt works, if i click out of table (it counts and display whole table sum, count...) and if i click on the one cell in table, it show still results of whole table

[danovkos]

This needs reselecting after each filter/unfilter

[vba]

Option Explicit

Private WithEvents App As Application

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

On Error Resume Next
With Application.WorksheetFunction
statusLine = "Sum=" & .Subtotal(109, Target)
statusLine = statusLine & "; Average=" & Round(.Subtotal(101, Target), 1)
statusLine = statusLine & "; Non-blank=" & .Subtotal(103, Target)
statusLine = statusLine & "; Min=" & .Subtotal(105, Target)
statusLine = statusLine & "; Max=" & .Subtotal(104, Target)
End With

Application.StatusBar = statusLine

End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub
[/vba]

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

sorry, i tried this code and thing, that it works..i will try it yet