| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need a function that will trap & manage a change in cell value
Scenario
A value exists in a cell The value in the cell is changed by data entry A date would be populated in an adjacent cell reflecting the date of change - for that cell only My worksheet has numerous places that this function would need to work |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I need a function that will trap & manage a change in cell val
Thank you, Tom. This response will be greatfully used and acknowledged.
What a response! "Tom Ogilvy" wrote: Right click on the sheet tab and select View Code. in the resulting module, paste in code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler ' use your list of cells to react to Set rng = Range("B1,C11,D12,M21,A5:A30,C1:C5") If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm" Target.Offset(0, 1).EntireColumn.AutoFit End If ErrHandler: Application.EnableEvents = True End Sub this uses the worksheet Change event. If you are not familiar with events, see Chip Pearson's site http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "Terry" wrote: Scenario A value exists in a cell The value in the cell is changed by data entry A date would be populated in an adjacent cell reflecting the date of change - for that cell only My worksheet has numerous places that this function would need to work |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I need a function that will trap & manage a change in cell val
How would this code be changed to update the change date to the comment of
the same cell being changed rather than an adjacent cell? "Terry" wrote: Thank you, Tom. This response will be greatfully used and acknowledged. What a response! "Tom Ogilvy" wrote: Right click on the sheet tab and select View Code. in the resulting module, paste in code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler ' use your list of cells to react to Set rng = Range("B1,C11,D12,M21,A5:A30,C1:C5") If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm" Target.Offset(0, 1).EntireColumn.AutoFit End If ErrHandler: Application.EnableEvents = True End Sub this uses the worksheet Change event. If you are not familiar with events, see Chip Pearson's site http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "Terry" wrote: Scenario A value exists in a cell The value in the cell is changed by data entry A date would be populated in an adjacent cell reflecting the date of change - for that cell only My worksheet has numerous places that this function would need to work |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I need a function that will trap & manage a change in cell val
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler ' use your list of cells to react to Set rng = Range("B1,C11,D12,M21,A5:A30,C1:C5") If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False Target.NoteText Text:=Format(Now, "mm/dd/yyyy hh:mm") End If ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Terry" wrote: How would this code be changed to update the change date to the comment of the same cell being changed rather than an adjacent cell? "Terry" wrote: Thank you, Tom. This response will be greatfully used and acknowledged. What a response! "Tom Ogilvy" wrote: Right click on the sheet tab and select View Code. in the resulting module, paste in code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler ' use your list of cells to react to Set rng = Range("B1,C11,D12,M21,A5:A30,C1:C5") If Not Intersect(Target, rng) Is Nothing Then Application.EnableEvents = False Target.Offset(0, 1).Value = Now Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy hh:mm" Target.Offset(0, 1).EntireColumn.AutoFit End If ErrHandler: Application.EnableEvents = True End Sub this uses the worksheet Change event. If you are not familiar with events, see Chip Pearson's site http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "Terry" wrote: Scenario A value exists in a cell The value in the cell is changed by data entry A date would be populated in an adjacent cell reflecting the date of change - for that cell only My worksheet has numerous places that this function would need to work |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating an if then function to manage deductions from different c | Excel Worksheet Functions | |||
| I need a function that will trap & manage a change in cell value | Excel Programming | |||
| How to manage/change the default month/day date format? | Excel Discussion (Misc queries) | |||
| Trap Sheet Name Change | Excel Programming | |||
| How To Trap Cell Value Change Event so determine XL calc sequence? | Excel Programming | |||
Linear Mode
