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?

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.
--
HTH...

Jim Thomlinson


"Matthew Wieder" wrote:

> 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?

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).

"Jim Thomlinson" wrote:

> 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.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Matthew Wieder" wrote:
>
> > 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?

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.

--
Regards,
Tom Ogilvy


"Matthew Wieder" wrote:

> 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).
>
> "Jim Thomlinson" wrote:
>
> > 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.
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Matthew Wieder" wrote:
> >
> > > 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?

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?

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Matthew Wieder" wrote:
>
> > 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).
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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.
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Matthew Wieder" wrote:
> > >
> > > > 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?

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" wrote:

> 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?
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Matthew Wieder" wrote:
> >
> > > 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).
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > 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.
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Matthew Wieder" wrote:
> > > >
> > > > > 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?

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?

"JLGWhiz" wrote:

> 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" wrote:
>
> > 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?
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Matthew Wieder" wrote:
> > >
> > > > 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).
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > 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.
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "Matthew Wieder" wrote:
> > > > >
> > > > > > 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?