Worksheet_Change event question

  • Thread starter Thread starter marwan hefnawy
  • Start date Start date
M

marwan hefnawy

the Worksheet_Change event detects if any cell changes its value, and
assigns this cell to the variable "Target". Thats very good.
My problem is, when I select a range of cells, then press "Delete" to empty
these cells, only the upper left most cell in the range is detected to be
changing its value, and only this cell initiates the Worksheet_Change event.
This is bad because all the cells in the selected range have changed their
values and the event procedure didn't detect them all.
Any Suggestions?
Thanks in advance.
 
Well, not exactly...

If you look at Target.Address when a range of cells is selected,
you'll see that Target is the *range* not the top left cell.

That can be a problem, because if, say A1:J10 is selected, and the
activecell is C6, making a change in C6 will return A1:J10 in
Target, with no indication of where the change actually occurred
(and, since we don't know whether the user tabbed or used enter to
complete the change, we can't backtrack from the
..moveafterreturndirection property.

You can build into your function a test for multiple cells being
selected, i.e.,

If Target.Count > 1 Then
'multiple cells
Else
'only one cell
End If

Remember that Target need not have contiguous cells - there could be
multiple areas, too.

Play around with this to get the scope of the problem:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox "Address: " & Target.Address & vbNewLine & _
"Count: " & Target.Count & vbNewLine & _
"Areas: " & Target.Areas.Count
End Sub
 
Back
Top