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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.