Run Macro on Change

[Ken Hudson]

I have the following code in Sheet1. When I change data in cell D5, it
invokes the FindInv macro. If I then try to clear a range of cells in Sheet1,
say D3:D4, the code below runs again and I get a type mismatch error. What do
I need to do to prevent this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D5") Then FindInv
End Sub


Thanks.
--
Ken Hudson

[Jim Thomlinson]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.address = "$D$5" Then FindInv
End Sub
--
HTH...

Jim Thomlinson


"Ken Hudson" wrote:

> I have the following code in Sheet1. When I change data in cell D5, it
> invokes the FindInv macro. If I then try to clear a range of cells in Sheet1,
> say D3:D4, the code below runs again and I get a type mismatch error. What do
> I need to do to prevent this?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target = Range("D5") Then FindInv
> End Sub
>
>
> Thanks.
> --
> Ken Hudson

[Ken Hudson]

Thanks, Jim.
This is my first foray into this type of VBA code. I'm moving from plain
vanilla to vanilla swirl.....
--
Ken Hudson


"Jim Thomlinson" wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.address = "$D$5" Then FindInv
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Ken Hudson" wrote:
>
> > I have the following code in Sheet1. When I change data in cell D5, it
> > invokes the FindInv macro. If I then try to clear a range of cells in Sheet1,
> > say D3:D4, the code below runs again and I get a type mismatch error. What do
> > I need to do to prevent this?
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target = Range("D5") Then FindInv
> > End Sub
> >
> >
> > Thanks.
> > --
> > Ken Hudson

[Jim Thomlinson]

Stick around... Tom might give out his recipe for Chocolate... I'm still
waiting... :-)
--
HTH...

Jim Thomlinson


"Ken Hudson" wrote:

> Thanks, Jim.
> This is my first foray into this type of VBA code. I'm moving from plain
> vanilla to vanilla swirl.....
> --
> Ken Hudson
>
>
> "Jim Thomlinson" wrote:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.address = "$D$5" Then FindInv
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Ken Hudson" wrote:
> >
> > > I have the following code in Sheet1. When I change data in cell D5, it
> > > invokes the FindInv macro. If I then try to clear a range of cells in Sheet1,
> > > say D3:D4, the code below runs again and I get a type mismatch error. What do
> > > I need to do to prevent this?
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target = Range("D5") Then FindInv
> > > End Sub
> > >
> > >
> > > Thanks.
> > > --
> > > Ken Hudson

[Tom Ogilvy]

Jim,
Maybe you can make it chocolate by telling him what his first attempt was
actually checking for.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

> Stick around... Tom might give out his recipe for Chocolate... I'm still
> waiting... :-)
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Ken Hudson" wrote:
>
> > Thanks, Jim.
> > This is my first foray into this type of VBA code. I'm moving from plain
> > vanilla to vanilla swirl.....
> > --
> > Ken Hudson
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.address = "$D$5" Then FindInv
> > > End Sub
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Ken Hudson" wrote:
> > >
> > > > I have the following code in Sheet1. When I change data in cell D5, it
> > > > invokes the FindInv macro. If I then try to clear a range of cells in Sheet1,
> > > > say D3:D4, the code below runs again and I get a type mismatch error. What do
> > > > I need to do to prevent this?
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target = Range("D5") Then FindInv
> > > > End Sub
> > > >
> > > >
> > > > Thanks.
> > > > --
> > > > Ken Hudson

[Jim Thomlinson]

I bet your chocolate tastes better but here goes...

The default property of a range such as Target or Range("D5") is the value
of the range, so the macro would run not only when D5 was changed, but
whenever the cell that was changed had a value equal to the value in D5...
What we need to check in this case is the address of the cell that was
changed, so by checking the address of the target (returns a string) against
"$D$5" we will know when cell D5 changed...

One day Tom I will be half the chef you are and then we will all be in
trouble... :-)
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

> Jim,
> Maybe you can make it chocolate by telling him what his first attempt was
> actually checking for.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Jim Thomlinson" wrote:
>
> > Stick around... Tom might give out his recipe for Chocolate... I'm still
> > waiting... :-)
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Ken Hudson" wrote:
> >
> > > Thanks, Jim.
> > > This is my first foray into this type of VBA code. I'm moving from plain
> > > vanilla to vanilla swirl.....
> > > --
> > > Ken Hudson
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target.address = "$D$5" Then FindInv
> > > > End Sub
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Ken Hudson" wrote:
> > > >
> > > > > I have the following code in Sheet1. When I change data in cell D5, it
> > > > > invokes the FindInv macro. If I then try to clear a range of cells in Sheet1,
> > > > > say D3:D4, the code below runs again and I get a type mismatch error. What do
> > > > > I need to do to prevent this?
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > If Target = Range("D5") Then FindInv
> > > > > End Sub
> > > > >
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Ken Hudson