Forum Discussion
Can we somehow use this unlisted selection change event?
- Oct 31, 2016
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 exit1
Dim 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