Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection_Change?
I have a situation where a lot of my code is executed by "Worksheet_Change".
However there is one part that I would like to add in and I think "Selection_Change" may be the answer - let me explain: If a cell is changed in the range ("C9:K9,M9:U9") then I would like the corresponding value in Offset(6) to be set to "". Something like:- Row 9 contains integers, row 15 text. For Each Cell In Range("C9:K9,M9:U9") If mycell.value is changed then mycell.offset(6).value = "" End if Next I know the above code is nonsense but a pointer would be useful. Sandy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection_Change?
It sounds like you want to react to a change in the cell--not just the selection
of one of those cells. If that's true: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRng As Range Dim myRngToCheck As Range Dim myCell As Range Set myRngToCheck = Me.Range("c9:k9,M9:U9") Set myRng = Intersect(Target, myRngToCheck) If myRng Is Nothing Then Exit Sub Application.EnableEvents = False On Error Resume Next 'just ignore any errors For Each myCell In myRng.Cells myCell.Offset(6, 0).Value = "" Next myCell On Error GoTo 0 Application.EnableEvents = True End Sub Sandy wrote: I have a situation where a lot of my code is executed by "Worksheet_Change". However there is one part that I would like to add in and I think "Selection_Change" may be the answer - let me explain: If a cell is changed in the range ("C9:K9,M9:U9") then I would like the corresponding value in Offset(6) to be set to "". Something like:- Row 9 contains integers, row 15 text. For Each Cell In Range("C9:K9,M9:U9") If mycell.value is changed then mycell.offset(6).value = "" End if Next I know the above code is nonsense but a pointer would be useful. Sandy -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection_Change?
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Union(Range("C9:K9"), Range("M9:U9")) If Intersect(Target, r) Is Nothing Then Exit Sub End If Application.EnableEvents = False Target.Offset(6, 0).Value = "" Application.EnableEvents = True End Sub -- Gary''s Student - gsnu200735 "Sandy" wrote: I have a situation where a lot of my code is executed by "Worksheet_Change". However there is one part that I would like to add in and I think "Selection_Change" may be the answer - let me explain: If a cell is changed in the range ("C9:K9,M9:U9") then I would like the corresponding value in Offset(6) to be set to "". Something like:- Row 9 contains integers, row 15 text. For Each Cell In Range("C9:K9,M9:U9") If mycell.value is changed then mycell.offset(6).value = "" End if Next I know the above code is nonsense but a pointer would be useful. Sandy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skip ... Selection_Change Event | Excel Programming |