SelectionChange

[drivinatwork]

I have a function that I want to have run using selection change.

The code for the function is:

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Currency
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

I have entered in the cell "=SumbyColor(A#,B#:B##)" and it works fine, the problem is I have to click in the cell and hit enter for it to update if a change is made. I want to use selectionchange to make it run automatically. So I made a private sub.

And my private sub is:

Private Sub WeekTotal_SelectionChange(ByVal Target As Range)
SumByColor
End Sub

I already have one Private Sub called Worksheet_SelectionChange, that's why I used WeekTotal instead of Worksheet. I would love to just add it to that one but that doesn't work either.

The problem is it will not update the sum when a selection is made, I inserted a message box and that worked so I know the code is running.

Can anyone help me with this. Thanks

 

[StephenCrump]

Re: SelectionChange - Help

Sub Worksheet_SelectionChange(ByVal Target As Range) is a required syntax. You can't customise the name of the Sub.

The easiest way to do this will be to add a code line: Calculate to your existing Worksheet_SelectionChange code.

But it's not ideal to be forcing a Calculate every time you change the Selection.

It also won't totally solve your issue, as you will still be able to sit on the same selection merrily making as many colour changes as you like, and your function won't update.

 

[Fluff]

Re: SelectionChange - Help

Add this line to the top of your function

Application.Volatile

 

[StephenCrump]

Re: SelectionChange - Help

Application.Volatile

The function should update correctly if any values in rRange change.

So I'm guessing the OP wants the function to update if any colours in rRange or CellColor change.

If that's right, Application.Volatile won't help.

 

[drivinatwork]

Re: SelectionChange - Help

Thanks, I added a line to just calculate the cells I needed.