Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell update formula
Hi
Does anyone know of a formula for Excel 2003 that will stamp the date & time in a particular cell if anything in that row has changed? Many thanks Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell update formula
Hi there,
If you want to do this for a single sheet, add the following to the specific Sheet object (ie not a separate module): Private Sub Worksheet_Change(ByVal Target As Range) Dim iRow As Integer iRow = Target.Row Cells(iRow, 1).Value = Now() End Sub ....or, if you want it to effect the whole workbook then add this to the Workbook object: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim iRow As Integer iRow = Target.Row Cells(iRow, 1).Value = Now() End Sub Basically, when the change event fires 'Target' becomes a range from which you can check the row number. You then use that in the cell address (Row x, Column 1) to place the date and time. Hope that helps. Best regards John "CDDAH NHS" wrote in message ... Hi Does anyone know of a formula for Excel 2003 that will stamp the date & time in a particular cell if anything in that row has changed? Many thanks Neil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell update formula
Excellent, thanks John!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell update formula
Be aware that the Now() will recalculate each time you open the file whether
there is a change or not. "CDDAH NHS" wrote: Excellent, thanks John! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell update formula
Good point.
You might want to change that to: Cells(iRow, 1).Value = CStr(Now()) Best regards John "JLGWhiz" wrote in message ... Be aware that the Now() will recalculate each time you open the file whether there is a change or not. "CDDAH NHS" wrote: Excellent, thanks John! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell update formula
=NOW() will recalculate, but what john is doing dose not put in a formula
it puts in the date and time so it will not recalculate -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "JLGWhiz" wrote in message ... Be aware that the Now() will recalculate each time you open the file whether there is a change or not. "CDDAH NHS" wrote: Excellent, thanks John! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update formula in column when another cell formula is changed | Excel Worksheet Functions | |||
Update Excel cell in formula not refreshing answer cell | Excel Discussion (Misc queries) | |||
update formula cell | Excel Programming | |||
What formula will get a cell to update when the month changes | Excel Worksheet Functions | |||
Update cell reference within a formula | Excel Discussion (Misc queries) |