Excel Event Deletion of a Range

[tkt_tang]

1. Enter an Excel Worksheet and select a range say, Cell E5.

2. Erase the contents of Cell E5 and the worksheet Change event is
fired.

3. And also ; Remove Cell E5 (albeit that it's replacement will follow
suit) and the worksheet Change event is fired as well.

4. The question is, how could it be possible to differentiate the
Erasure or Removal of Cell E5 by virtue of the Change event ?

5. Furthermore, what if, instead of a single cell, the affected range
of the Change event is multiple-cell, multiple-row or multiple-column ?
Could the respective conditions be coded to identify the resultant the
erasure or removal thereof ?

6. Please share your experience. Regards.

 

[Bob Phillips]

AFAIK there is no way to differentiate by the actual thing that triggered
the change event. If you want to trap that, you would have to add some code
that trapped it, and then test that within the event, such as counting the
rows in the selection event, then re-count on change. But this gets very
cumbersome, and ther variables are many.

Not sure what you mean by 5. You can test for a multi-cell change, just by
counting the cells in Target.

If Target.Count > 1 Then

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[tkt_tang]

Mr. Bob Phillips,

Thank you for your reply to my query.

In another separate matter ; I have added Application.EnableEvents =
False (paired with Application.EnableEvents = True respectively on
exit) in all the Event Handlers coded.

However, when it is determined that the worksheet has entered into a
particular event, other events followed suit. This observation is
identifiable by the MsgBoxes planted at the strategic locations.

Please share your comments. Regards.

 

[Bob Phillips]

I code it like this, using Worksheet_Change as an example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
'my code here

ws_exit:
Application.EnableEvents = True
End Sub

That way events are imeediately turned off, stop the event cascade, but
re-instated at the end, even in the event of an error.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[tkt_tang]

Mr. Bob Phillips,

Thank you again for your reply to my query. Regards.