Cause of SheetChange Event

[Matthew Wieder]

We've got an event handler on the SheetChange event that needs to do some
work if the user entered or changed values in teh sheet. However, we have
found that the SheetChange event fires far more often then in this case (such
as when the user deletes a column) and we need a way to know whether we
should contiune on with our handler because the user entered data, or we
should exit the function because something else triggered the event. How
can we do this? Is there some other event that gets fired on column deletion
that we could set a flag in? Is there a way within the sheetchange event to
know wahat kind of change triggered the call?

 

[Jim Thomlinson]

Just check the count of the target.

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count > 1 then exit sub
msgbox "Do Some Stuff"
End Sub

In the above axample the message box will only come up if only one cell was
changed.

 

[Matthew Wieder]

Your suggestion would differentiate between whether a single cell was changed
vs a group. I need my event handler to continue work even on a group of
cells if data was changed (for example via a paste operation).

 

[Tom Ogilvy]

I think Jim was saying there is no built in support for what you want to do -
you are left with cleverness as the only option.

 

[Matthew Wieder]

Certainly then, this issue has come up and there are some solutions - since
testing if the target is more then 1 cell doesn't work in my case, perhaps
some could share their cleverness?

 

[JLGWhiz]

You would need to define the parameters which you want your changes to
trigger a reaction and then writhe a conditional If...Then...Else...End If
statement or a Select Case statement that either includes or excludes
conditions so that the ChangeEvent only triggers when you want it to. That's
pretty clever.

 

[Matthew Wieder]

Perhaps your solution is to clever for me - I don't understand how I would
determine wether the SheetChange was called due to column deletion vs being
called due to data changing. That is the issue this thread is dealing with.
Can you explain how you solve this issue?