Can we somehow use this unlisted selection change event?

[Ivan Bosnić]

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.

[#2]

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

[#3]

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

[#4]

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. :)

[#5 · Accepted solution]

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]

Thanks. I totally forgout about timer usage. :)

[#7]

You're welcome 

Best of luck in the main comp