Change event

J

JohnL

I am attempting to write code that does the following:
Every time the User inputs data in cell L36
Then cells E40 thru E43 are cleared.
Thanks
John
 
D

Don Guillett

Right click sheet tab>view code>copy/paste this
Private Sub Worksheet_Change(ByVal Target As Range)
'Every time the User inputs data in cell L36
'Then cells E40 thru E43 are cleared.
If Target.Address <> Range("L36").Address Then Exit Sub
Range("e40:e43").ClearContents
End Sub
 
J

JLGWhiz

Paste this in your Sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("L36") Then
If Target <> "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub
 
J

Joel

If you copy multiple cell an paste them over cell L36 you need to use a loop
like the code below:

Sub worksheet_change(ByVal target As Range)

For Each cell In target
If Not Intersect(cell, Range("L36")) Is Nothing Then
Range("E40:E43").ClearContents

End If
Next cell
End Sub
 
J

JLGWhiz

This is the correct way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("L36"), Target) Is Nothing Then
If Target <> "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub
 
J

JohnL

Don, This worked perfectly! I don't mean to look a gift horse in the mouth,
but if there is an If...Then statement why isn't there an End If?
Thanks again Don.

John
 
D

Don Guillett

If its on ONE line then no end if needed.OR, if you use a continuation
character at the end of the line (space and underscore)

If Target.Address <> Range("L36").Address _
Then Exit Sub
'no end if
 
S

ShaneDevenshire

Hi John,

The reason JL says this is the correct way is because it is the more general
solution. Consider what you would need to write if the range where users
could enter data which triggered your macro was A1:A1000, using the first
method you would need to do 1000 IF's, so hard! In JL's case you would change
the Range("L36") to read Range("A1:A1000"), so simple!

In both cases you can use the sortcut notation [L36] or [A1:A1000] in which
case there is no need for the quotes or the Range(). It's faster to enter,
but the auto complete feature doesn't work with it and it may not be as
flexible in some situations.
 
J

JohnL

Thank you Shane for the explanation. It is responses like yours which make
this not only a discussion group but also a learning group. Thanks again.
John

ShaneDevenshire said:
Hi John,

The reason JL says this is the correct way is because it is the more general
solution. Consider what you would need to write if the range where users
could enter data which triggered your macro was A1:A1000, using the first
method you would need to do 1000 IF's, so hard! In JL's case you would change
the Range("L36") to read Range("A1:A1000"), so simple!

In both cases you can use the sortcut notation [L36] or [A1:A1000] in which
case there is no need for the quotes or the Range(). It's faster to enter,
but the auto complete feature doesn't work with it and it may not be as
flexible in some situations.

--
Thanks,
Shane Devenshire


JLGWhiz said:
This is the correct way to do it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("L36"), Target) Is Nothing Then
If Target <> "" Then
Range("E40:E43").ClearContents
End If
End If
End Sub
Click to expand...
Click to expand...
 
Top