Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event
Hi,
I am struggling to do something that I think should be easy, but I can't figure it out. I have a sheet within which I need to respond to specific changes: If a cell in column H or column L is chnaged, I need to chnage the value in column M of the same row. I don't know which row will be altered, so I am trying to use a Worksheet Change Event to trap the change with: Private Sub Worksheet_Change(ByVal Target As Range) Select Case True Case Target.Column=8 or Target.Column=12 <update cell "M" in Target.Row End Select End Sub What I can't figure out is the <update cell "M" in Target.Row code. I can capture the Target.Row to a string but can't covert the string to a range to update cell M Target.Row and I can't help thinking that there must be a better way of doing things anyway. This is in Office '07 under Vista FWIW Any suggestions on how I achieve this would be gratefully received. Hope this makes sense! TIA Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event
Try the below. Right click the sheet tabView Code and paste the below code
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("H:H,L:L")) Is Nothing Then Range("M" & Target.Row) = "Changed H/L" End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Risky Dave" wrote: Hi, I am struggling to do something that I think should be easy, but I can't figure it out. I have a sheet within which I need to respond to specific changes: If a cell in column H or column L is chnaged, I need to chnage the value in column M of the same row. I don't know which row will be altered, so I am trying to use a Worksheet Change Event to trap the change with: Private Sub Worksheet_Change(ByVal Target As Range) Select Case True Case Target.Column=8 or Target.Column=12 <update cell "M" in Target.Row End Select End Sub What I can't figure out is the <update cell "M" in Target.Row code. I can capture the Target.Row to a string but can't covert the string to a range to update cell M Target.Row and I can't help thinking that there must be a better way of doing things anyway. This is in Office '07 under Vista FWIW Any suggestions on how I achieve this would be gratefully received. Hope this makes sense! TIA Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Event
Thanks
"Jacob Skaria" wrote: Try the below. Right click the sheet tabView Code and paste the below code Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("H:H,L:L")) Is Nothing Then Range("M" & Target.Row) = "Changed H/L" End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Risky Dave" wrote: Hi, I am struggling to do something that I think should be easy, but I can't figure it out. I have a sheet within which I need to respond to specific changes: If a cell in column H or column L is chnaged, I need to chnage the value in column M of the same row. I don't know which row will be altered, so I am trying to use a Worksheet Change Event to trap the change with: Private Sub Worksheet_Change(ByVal Target As Range) Select Case True Case Target.Column=8 or Target.Column=12 <update cell "M" in Target.Row End Select End Sub What I can't figure out is the <update cell "M" in Target.Row code. I can capture the Target.Row to a string but can't covert the string to a range to update cell M Target.Row and I can't help thinking that there must be a better way of doing things anyway. This is in Office '07 under Vista FWIW Any suggestions on how I achieve this would be gratefully received. Hope this makes sense! TIA Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
apply cell change event to single column - WorksheetChange Event | Excel Programming | |||
How do I change a Worksheet_change event to a beforesave event? | Excel Programming | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
Change event and calculate event | Excel Programming | |||
change event/after update event?? | Excel Programming |