|
Change event
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 |
Change event
Right click sheet tabview codecopy/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 -- Don Guillett Microsoft MVP Excel SalesAid Software "JohnL" wrote in message ... 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 |
Change event
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 "JohnL" wrote: 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 |
Change event
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 "JLGWhiz" wrote: 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 "JohnL" wrote: 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 |
Change event
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 "JohnL" wrote: 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 |
Change event
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 "Don Guillett" wrote: Right click sheet tabview codecopy/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 -- Don Guillett Microsoft MVP Excel SalesAid Software "JohnL" wrote in message ... 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 |
Change event
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" wrote: 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 "JohnL" wrote: 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 |
Change event
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" wrote: 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" wrote: 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 "JohnL" wrote: 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 |
| All times are GMT +1. The time now is 07:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com