Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a last modified date using the worksheet change event
I have an Excel 2003 workbook to manage large amounts of data. I want to
track changes made to the data, but I don't want to use Excel's track changes feature as I don't like the display options. I created two columns in my worksheet, Last Modified Date and Last Modified Time. I want the values in these columns to be updated to the system date and time, any time the value of any cell in the same row is changed. The following code off the worksheet change event is close to what I want, but when ranges of cells are pasted into my working area, I want ALL the last modified date and times to be set for each row that was part of the paste. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Union(Target, Range("A8:O65536")).Address = "$A$8:$O$65536" Then Cells(Target.Cells.Row, 20) = Date Cells(Target.Cells.Row, 21) = Time End If Application.EnableEvents = True End Sub What do I use to get all the date/time values (columns 20 and 21) to change for all the rows in my range? -- Thanks, Bruce Bolio |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating a last modified date using the worksheet change event
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell as Range Application.EnableEvents = False If Union(Target, Range("A8:O65536")).Address = "$A$8:$O$65536" Then for each cell in Target Cells(cell.Row, 20) = Date Cells(cell.Row, 21) = Time Next End If Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Bruce Bolio" wrote: I have an Excel 2003 workbook to manage large amounts of data. I want to track changes made to the data, but I don't want to use Excel's track changes feature as I don't like the display options. I created two columns in my worksheet, Last Modified Date and Last Modified Time. I want the values in these columns to be updated to the system date and time, any time the value of any cell in the same row is changed. The following code off the worksheet change event is close to what I want, but when ranges of cells are pasted into my working area, I want ALL the last modified date and times to be set for each row that was part of the paste. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Union(Target, Range("A8:O65536")).Address = "$A$8:$O$65536" Then Cells(Target.Cells.Row, 20) = Date Cells(Target.Cells.Row, 21) = Time End If Application.EnableEvents = True End Sub What do I use to get all the date/time values (columns 20 and 21) to change for all the rows in my range? -- Thanks, Bruce Bolio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating a DATE, only if the page has been modified. | Excel Discussion (Misc queries) | |||
Excel Updating Modified Date When I Don't Save File | Excel Discussion (Misc queries) | |||
Use date modified to change format & create filter to track change | Excel Worksheet Functions | |||
Updating another sheet using an On Change event | Excel Programming |