Assuming in column A from row 1 to 5 the values are:
10
20
30
40
50
When user select cells A1 and A2,
on the bottom right of Excel app it show:
Average:15 Count:2 Sum:30
When user select cells A1, A3 and A5,
on the bottom right of Excel app it show:
Average:30 Count:3 Sum:90
So that information change automatically
each time the user add a cell to the selection.
I wonder if it's possible to write a code,
so that kind of "automation" for the Sum
can be seen in a user form's label.
So far I've tried a code :
Private Sub UserForm_Initialize()
Me.Label1.Caption = Application.Sum(Selection)
End Sub
But with the code above, I need to provide a button for the user to click to calculate the sum of his selection in order the Label1.Caption show the Sum result.
I've also tried a code in worksheet like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Range("B1").Value = Application.Sum(Selection)
End If
End Sub
Cell B1 did automatically show the sum result each time a cell in range A1 to A5 is selected.
But then I don't know how to implement it to the User Form label, and actually it doesn't work as my expectation
because with this code, cell B1 will show the sum result only after I release the left click of the mouse,
while I'm expecting it show the sum as long as the user drag a selection (without releasing the left click mouse) in the range just like the "average/counts/sum automation" at the bottom right of the Excel App.
Any kind of respond would be greatly appreciated.
Thank you in advanced.
Bookmarks