Solved: multiply statusbar

Printable View

05-27-2009, 07:18 AM
danovkos

Solved: multiply statusbar

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

Code:

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
05-27-2009, 09:30 AM
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
05-27-2009, 10:48 PM
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 :)
05-27-2009, 11:41 PM
anandbohra

Quote:

Originally Posted by 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 :)

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)
05-28-2009, 12:29 AM
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.

Code:

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

thx a lot
05-28-2009, 01:01 AM
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".
05-28-2009, 01:08 AM
danovkos

PERFECT :)
THANK YOU VERY MUCH
05-28-2009, 01:13 AM
Bob Phillips

Quote:

Originally Posted by anandbohra

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.
05-28-2009, 01:18 AM
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
05-28-2009, 01:32 AM
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?
05-28-2009, 01:52 AM
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?
05-28-2009, 02:25 AM
anandbohra

Quote:

Originally Posted by xld

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)
05-28-2009, 02:28 AM
anandbohra

Quote:

Originally Posted by danovkos

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

Quote:

Originally Posted by anandbohra

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]
05-28-2009, 02:58 AM
danovkos

yes, great :)
works perfect :)
thx
05-28-2009, 02:59 AM
Bob Phillips

Quote:

Originally Posted by 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?

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
05-28-2009, 02:59 AM
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 :)
05-28-2009, 03:05 AM
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
05-28-2009, 03:12 AM
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 :(
05-28-2009, 03:17 AM
danovkos

Quote:

Originally Posted by xld

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

Quote:

Originally Posted by 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

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.
05-28-2009, 04:24 AM
danovkos

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 :)
05-28-2009, 04:29 AM
Bob Phillips

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

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?
05-28-2009, 10:20 PM
anandbohra

Quote:

Originally Posted by xld

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:
05-29-2009, 01:36 AM
Bob Phillips

Quote:

Originally Posted by anandbohra

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.
05-29-2009, 01:45 AM
danovkos

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
05-31-2009, 11:13 PM
danovkos

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
06-01-2009, 12:01 AM
danovkos

.mistake
07-23-2009, 12:28 AM
danovkos

hi,
pls. why this code for module

Quote:

Originally Posted by xld

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]

doenst works with modified statusbar command which i use ?

Code:

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

All times are GMT -7. The time now is 09:28 PM.