Incorrect target range in Worksheet_Change event

Question

I am an Excel-VBA newcomer and I am trying to write a short piece of code which is triggered by somebody changing the value of cells in a worksheet. It should set the value of the changed cell to zero if it's less than zero. The code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Debug.Print Target.Address
 If Target.Column = 6 Then
  For Each Cell In Target.SpecialCells(xlCellTypeConstants, 3)
     If Cell.Value < 0 Then
        Cell.Value = 0
     End If
  Next
 End If
End Sub

Now what happens is that when I change the value of any cell in column 6, every cell in the sheet containing numbers less than zero is also changed to zero.

I was thinking that the "Target" Object that is created for the Worksheet_Change event handler would only contain the cell/cells changed, and thus my code would only change the value of cells that were modified and triggered the event in the first place.

I tried to help myself by using Debug.Print to output the address of the object. It printed out the address of every cell in the sheet with a value less than zero, so I assume the handler ran several times.

I actually found a workaround for the problem itself but my questions are these: how did I fail in using the Worksheet_Change event and what can I do in the future to not have such problems?

Answer 1

EDIT 1: Updated code with bug fix suggested in comments

EDIT 2: Updated code with error handling to deal with Array Formulae

In answer to your question

[1] how did I fail in using the Worksheet_Change event and [2] what can I do in the future to not have such problems?

1. Technically you didn't
2. Nothing for this type of problem, although the following rule helps in general

You are correct in your understanding of the Targetobject. Your code failed because SpecialCells doesn't like a single cell to operate on. Give it one and it expands it to the entire sheet! Give it anything else and it works just fine.

The reason your Debug.Print displays all the cells is that every time your code changes a cell, another change event is triggered. Luckily for you the second one finds a zero so it doesn't trigger another one. The following general rule should help avoid a lot of problems, just not this particular one:

• Always surround any code in an event handler that changes any part of the workbook with Application.EnableEvents.

To fix you code so it works, simply remove the SpecialCells method call. Since you are using Cell.Value instead of the highly recommended Cell.Value2 (see here), VBA implicit type converts numbers formatted as text to actual numbers for you. Thus the code works both on numeric and text values.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  'Debug.Print Target.Address;
  Application.EnableEvents = False
    For Each Cell In Target '.SpecialCells(xlCellTypeConstants, 3)
      If Cell.Column = 6 And Cell.Value < 0 Then
        On Error GoTo Error:
        Cell.Value = 0
        On Error GoTo 0
      End If
    Next
GoTo ExitSub:
Error:
  If Err.Number = 1004 Then ' 1004 -> "You cannot change part of an array."
    'Application.Undo ' Uncomment to disallow array entering a negative value formula into/across column 6
    MsgBox "A cell could not be zeroed as" & vbCr & "it is part of an array formula.", vbExclamation, "Microsoft Office Excel"
    On Error GoTo 0
  Else
    On Error GoTo 0
    Resume
  End If
ExitSub:
  Application.EnableEvents = True
End Sub

_Notes:

_{- Update 1: Code now correctly deals with multi-cell changes.}

_{- Update 2: Code now traps error 1004 to deal with entering Array Formulae. It can either allow the array formula to be entered resulting in negative values in column 6, or stop the entry altogether.}