Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert date when data is modified
I have rows of data that I will periodically update. What I want to do is
have a cell at the end of the row that shows the date when data in just that row was last updated. For example, the rows might look like this: 13 42 23 45 90 ModifyDateRow1 52 32 97 74 37 ModifyDateRow2 I assumed I could do it with VBA, but when I tried to write it, I found that I wasn't sure how to proceed. The code I tried is below, but the TODAY function doesn't work ("Object doesn't support this property or method."). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim WS As Worksheet Set WS = Worksheets("Sheet1") If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then With WS.Cells(Target.Row, 6) .Value = Application.WorksheetFunction.Today() .NumberFormat = "mm/dd/yyyy" End With Exit Sub End If End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert date when data is modified
Look he
http://www.mcgimpsey.com/excel/timestamp.html -- Kind regards, Niek Otten Microsoft MVP - Excel "Horatio J. Bilge, Jr." wrote in message ... |I have rows of data that I will periodically update. What I want to do is | have a cell at the end of the row that shows the date when data in just that | row was last updated. | For example, the rows might look like this: | 13 42 23 45 90 ModifyDateRow1 | 52 32 97 74 37 ModifyDateRow2 | | I assumed I could do it with VBA, but when I tried to write it, I found that | I wasn't sure how to proceed. The code I tried is below, but the TODAY | function doesn't work ("Object doesn't support this property or method."). | | Option Explicit | Private Sub Worksheet_Change(ByVal Target As Range) | Dim WS As Worksheet | Set WS = Worksheets("Sheet1") | If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then | With WS.Cells(Target.Row, 6) | .Value = Application.WorksheetFunction.Today() | .NumberFormat = "mm/dd/yyyy" | End With | Exit Sub | End If | End Sub | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert date when data is modified
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) Dim WS As Worksheet Set WS = Worksheets("Sheet1") If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then With WS.Cells(Target.Row, 6) .Value = Format(Date, "mm/dd/yyyy") End With Exit Sub End If End Sub Gord Dibben MS Excel MVP On Tue, 23 Oct 2007 13:37:01 -0700, Horatio J. Bilge, Jr. wrote: I have rows of data that I will periodically update. What I want to do is have a cell at the end of the row that shows the date when data in just that row was last updated. For example, the rows might look like this: 13 42 23 45 90 ModifyDateRow1 52 32 97 74 37 ModifyDateRow2 I assumed I could do it with VBA, but when I tried to write it, I found that I wasn't sure how to proceed. The code I tried is below, but the TODAY function doesn't work ("Object doesn't support this property or method."). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim WS As Worksheet Set WS = Worksheets("Sheet1") If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then With WS.Cells(Target.Row, 6) .Value = Application.WorksheetFunction.Today() .NumberFormat = "mm/dd/yyyy" End With Exit Sub End If End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert date when data is modified
Thanks. That worked well.
~ Horatio "Gord Dibben" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim WS As Worksheet Set WS = Worksheets("Sheet1") If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then With WS.Cells(Target.Row, 6) .Value = Format(Date, "mm/dd/yyyy") End With Exit Sub End If End Sub Gord Dibben MS Excel MVP On Tue, 23 Oct 2007 13:37:01 -0700, Horatio J. Bilge, Jr. wrote: I have rows of data that I will periodically update. What I want to do is have a cell at the end of the row that shows the date when data in just that row was last updated. For example, the rows might look like this: 13 42 23 45 90 ModifyDateRow1 52 32 97 74 37 ModifyDateRow2 I assumed I could do it with VBA, but when I tried to write it, I found that I wasn't sure how to proceed. The code I tried is below, but the TODAY function doesn't work ("Object doesn't support this property or method."). Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim WS As Worksheet Set WS = Worksheets("Sheet1") If Not (Application.Intersect(Target, Range("A1:E20")) Is Nothing) Then With WS.Cells(Target.Row, 6) .Value = Application.WorksheetFunction.Today() .NumberFormat = "mm/dd/yyyy" End With Exit Sub End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert the date the file was last modified | Excel Discussion (Misc queries) | |||
insert the date the file was last modified | Excel Discussion (Misc queries) | |||
Insert date modified of external file | Excel Discussion (Misc queries) | |||
How do I insert date modified in an Excel document? | Excel Discussion (Misc queries) | |||
EXCEL - is there a way to insert "date modified" into | Excel Worksheet Functions |