| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use Macro To Change Which Macro Assigned To Command Button | Excel Discussion (Misc queries) | |||
| Insert row at change macro - how to change it. | Excel Discussion (Misc queries) | |||
| macro that will change the font of a cell if i change a value | Excel Discussion (Misc queries) | |||
| Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
| How do I change macro text with another macro? | Excel Discussion (Misc queries) | |||
Linear Mode
