Run Macro on Change
 

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

Run Macro on Change
 

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

Run Macro on Change
 

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

Run Macro on Change
 

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

Run Macro on Change
 

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

Run Macro on Change
 

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