PDA

View Full Version : Solved: multiply statusbar



danovkos
05-27-2009, 07:18 AM
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
05-27-2009, 09:30 AM
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


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
05-27-2009, 10:48 PM
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
05-27-2009, 11:41 PM
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?:dunno

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
05-28-2009, 12:29 AM
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
05-28-2009, 01:01 AM
Try this

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


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
05-28-2009, 01:08 AM
PERFECT :)
THANK YOU VERY MUCH

Bob Phillips
05-28-2009, 01:13 AM
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?:dunno

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
05-28-2009, 01:18 AM
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
05-28-2009, 01:32 AM
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
05-28-2009, 01:52 AM
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
05-28-2009, 02:25 AM
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
05-28-2009, 02:28 AM
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
statusLine = statusLine & "; Vis =" & .CountA(Target.SpecialCells(xlCellTypeVisible))

Bob Phillips
05-28-2009, 02:49 AM
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



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


and this to a standard code module




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

danovkos
05-28-2009, 02:58 AM
yes, great :)
works perfect :)
thx

Bob Phillips
05-28-2009, 02:59 AM
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



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


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
05-28-2009, 02:59 AM
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
05-28-2009, 03:05 AM
Hi XLD
tried your code but it is not working

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

Call ShowSBInfo


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

danovkos
05-28-2009, 03:12 AM
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
05-28-2009, 03:17 AM
This needs reselecting after each filter/unfilter



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


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

Bob Phillips
05-28-2009, 04:04 AM
Hi XLD
tried your code but it is not working

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

Call ShowSBInfo


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

Works fine for me.

You need to understand that the menu item is a toggle. Starts with no statusbar display, you click it and a check mark appears and the statusbar displays. Cliuck again, the check mark goes and no statusbar display.

danovkos
05-28-2009, 04:24 AM
i am interesting now in, if it is possible to get to clipboard the amount from status bar (from the new funcion) after clicking on funcion. F.e. after clicking on SUM, get to clip amount of sum, after click count, give count...
it will be very helpfull
but this is only my imagine :)

Bob Phillips
05-28-2009, 04:29 AM
Don't think so. I can't see how we can determine that you clicked on the statusbar and whereabouts in that window.

danovkos
05-28-2009, 04:48 AM
ok and is it possible only for one of our funcions
i mean, that after clicking on statusbar it get SUM to clip

what do you mean?

anandbohra
05-28-2009, 10:20 PM
Works fine for me.

You need to understand that the menu item is a toggle. Starts with no statusbar display, you click it and a check mark appears and the statusbar displays. Cliuck again, the check mark goes and no statusbar display.

how it can work fine as "Sbinfo " is not defined in your code it is "ShowSBInfo "

pl check at your end & now it works fine for me also :yes

You are great XLD :beerchug:
:friends: :friends: :friends:

Bob Phillips
05-29-2009, 01:36 AM
how it can work fine as "Sbinfo " is not defined in your code it is "ShowSBInfo "

I did correct that bit, but that was only initialising the statusbar, it wasn't fundamental to the functionality.

danovkos
05-29-2009, 01:45 AM
guys i have other idea,
is it possible to create button wich will insert concrete amount from status bar?
f.e. - one button for sum, other for avarage ...?

i think this way
it will remember last value, greater as 0. I select range, it show f.e. sum = 1000 and then i click on blank cell (the sum of this cell will be 0) and if i click on button, it will paste 1000
:)
it will be great...
thx

danovkos
05-31-2009, 11:13 PM
pls. one more question, i hope it will be the last to this topic.
Code works great, but now, if i run my other macros, it will always look for change in cell. Always, when i have in my other code any command, which change content of cell, the macro continues/swich to the this statusbar macro (i see it in debug)...and i don know why, some codes, stop works. How can i stop using "status bar macro" during running other macro? maybe som command "stop using privat macro statusbar"?
Can something like this?
thx

danovkos
06-01-2009, 12:01 AM
.mistake

danovkos
07-23-2009, 12:28 AM
hi,
pls. why this code for module



and this to a standard code module



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


doenst works with modified statusbar command which i use ?


statusLine = statusLine & "SUMA v Sk=" & Format(.Subtotal(109, Target) * 30.126, "##,##0.00 " & "Sk")
statusLine = statusLine & " Priemer=" & Format(Round(.Subtotal(101, Target), 1), "##,##0.00")
statusLine = statusLine & " || Počet=" & .Subtotal(103, Target)
statusLine = statusLine & " Suma=" & Format(.Subtotal(109, Target), "##,##0.00")

it is appear nothing in statusbar :(
thx