CODEWITHSUNDEEP
excelvba
Manu
Manu

Reputation: 4137

Cannot get Worksheet_change to work in VBA

I've read many previous similar questions and answers and still cannot get my code to work.

I want to compute the sum of some cells depending on the background color of some other cells. In particular, I include a cell (x,y) in the sum only if the color of the cell (x,z) is the chosen one (RGB(0, 176, 80)).

The FIRST sum is correctly computed, but, when I modify a cell in the range F1:G100, I do get notified ("pd"), but no changes happen on the destination cell.

This code is in the Sheet where I want the changes to happen.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("F1:G100")) Is Nothing Then
    Cells(11, 14).Value = Sum1()
    Cells(12, 14).Value = Sum2()
    MsgBox "pd"
End If
End Sub
'-----------------------------------------------------------------------
Public Function Sum1() As Double
Dim N As Long
Sum1 = 0

For N = 4 To 100
    colorBackground = Cells(N, 2).Interior.Color
    If colorBackground = RGB(0, 176, 80) Then
        Sum1= Sum1+ Cells(N, 6).Value
    End If
Next N      
End Function
'-----------------------------------------------------------------------
Public Function Sum2() As Double
Dim N As Long
Sum2 = 0

For N = 4 To 100
    colorBackground = Cells(N, 2).Interior.Color
    If colorBackground = RGB(0, 176, 80) Then
        Sum2= Sum2+ Cells(N, 7).Value
    End If
Next N 
End Function

Upvotes: 1

Views: 643

Answers (1)

Erik Eidt
Erik Eidt

Reputation: 26646

This may have nothing to do with the problem, but, I'll tell you what I don't like in the above code, is the naked (or unqualified) use of Cells. By definition of the Excel object model:

Cells: Returns a Range object that represents all the cells on the active worksheet. (If the active document isn’t a worksheet, this property fails.)

So, what can happen is that a change happens when the sheet is not the active sheet, and then the event handler runs with "Cells" referring to what is probably not the intended sheet.

While it is harder to imagine how a change is accomplished directly by the user without the worksheet being the active one, certainly VBA code can cause a change on a non-active sheet, and this does trigger that (non-active sheet's) Worksheet_Change event handler without altering the current notion of what the active sheet is.

You might consider changing these from Cells (or Application.Cells, equally bad in this context) to Target.Parent.Cells instead (in Sum1 and Sum2 also, you'll have to pass them Target.Parent (or Target)).

(Can't tell since Sum1 and Sum2 are public, so if you're using Sum1 or Sum2 as UDF's as well as being called here, then I'd have the UDF's be a wrapper that recovers the worksheet, via Application.ThisCell or something, then calling another function that does the work (and is private so cannot be called as UDF).)

Upvotes: 1

Related Questions