Forum Discussion
Solved
Can we somehow use this unlisted selection change event?
Hi!
Can we somehow in VBA use event which updates this values:
It's some advanced version of SelectionChange event which isn't listed in VBA editor. :)
Thanks.
Oh I see!
I dont think theres an event for this but you could probably get there with a combination something like..
Start a timer on the mouse down trigger, pull the status bar total every x milliseconds until it meets the value you want...?
6 Replies
Hi Ivan :)
Could you re-phrase your question please?
I think you're trying to ask about editing the information in the status bar, but I don't understand how it's related to the event trigger SelectionChange.
If you're just after your own values there, the closest I know if is to hide the default status bar items and then display your own custom text with Application.StatusBar. It sits on the left though I think, never tried pushing it to the right side over the standard summaries as I find them useful :p
Tiara
- Hi Nick and Tiara,
It seems that I didn't explain my problem good enough. :)
I don't want update or add something to status bar. We have SelectionChange and SheetSelectionChange events which triggers after I select some range and release mouse button or keyboard key. I know how to use them and their limitation.
I want event which triggers during range selecting, when number of selected cells is changing and mouse button is still pressed. If that event exists, it's probably used for updating values I yellowed on print screen in first post. If you press mouse button and start moving mouse you'll select more and more cells. After every new cell selected, Excel updates count/sum/average... values in status bar. If I try to do same with SelectionChange and SheetSelectionChange events, values will only be updated after I release mouse button.
I hope that my description is now more understandable. :)
Thanks for help. :)Oh I see!
I dont think theres an event for this but you could probably get there with a combination something like..
Start a timer on the mouse down trigger, pull the status bar total every x milliseconds until it meets the value you want...?
This is what I use. It prints to the left side and leaves the numbers that you referenced alone. It is a Class Module in my Personal workbook.
Nick
Public WithEvents mApp As Application
Private Sub mApp_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
'http://www.dicks-blog.com/archives/2004/12/09/
On Error GoTo exit1Dim vAvg As Variant 'to account for errors
Dim lCells As Double
Dim lCnt As Double
'Dim dMax As Double
Dim vMax As Variant
'Dim dMin As Double
Dim vMin As Variant
'Dim dSum As Double
Dim vSum As Variant
Dim dCnta As Double
'Make sure selection is a range
If TypeName(Target) = "Range" Then
'Only when more than one cell is selected
If Target.Cells.Count > 1 Then
'Caclulate stats
vAvg = Application.Average(Target)
lCells = Target.Cells.Count
lCnt = Application.Count(Target)
'dMax = Application.Max(Target)
vMax = Application.Max(Target)
'dMin = Application.Min(Target)
vMin = Application.min(Target)
'dSum = Application.Sum(Target)
vSum = Application.Sum(Target)
dCnta = Application.CountA(Target)Application.StatusBar = "Average: " & CStr(vAvg) & " | " & _
"Cell Count: " & lCells & " | " & _
"Count Nums: " & lCnt & " | " & _
"CountA: " & dCnta & " | " & _
"Max: " & CStr(vMax) & " | " & _
"Min: " & CStr(vMin) & " | " & _
"Sum: " & CStr(vSum) & " | "
Else
'Return control of statusbar
Application.StatusBar = False
End If
Else
Application.StatusBar = False
End If
Exit Sub
exit1:End Sub
