Hi everyone, I have monetary values in F2:F10. When a value is changed, I manually insert the date in corresponding K2:K10. The date does NOT change on subsequent days, only when I manually change it. How can I auto this process so that the date will be renewed each time I change the monetary value? Thanks for any help.
=TODAY() always returns the computer’s date.
1 Like
Hi, well that won’t work because the date will change if the value doesn’t.
Why do you want to overwrite data of the past? You lose the ability to analyse them. The usual approach would append new data as new records with time stamps as demonstrated in this demo database: SimpleInventory_embedded_FB.odb (55.3 KB)
where you enter ingoing and outgoing items in forms “Input” and “Output” with automatic time stamp.
1 Like
You’re right, because only the interactive edit of the cells is tracked, without regard to the change of the value. In addition, changes are not tracked if a cell in the observed range has formula that depends on cells outside the range, etc.
Another approach is proposed based on the use of the XMofifyListener.
PutChangedDate2.ods (9.4 KB)
2 Likes
Thank’s for translate and best explanation. I would like to add
If the new monetary value < old monetary value => Change date
1 Like
I found. thank’s
1 Like
Thanks for your help. Both macro versions work OK and that’s what I’ve been looking for.
One more question - how to copy macros from one .ods file into another?
Please open a new question for this. You may have noticed the site complains, when we add to solved topics. And others may find answers on moving macros better, if not “hidden” here.
Noted. Thanks.
What if created a function: ISMODIFIED() ?
there is an undefined variable: Error BASIC
Sub RangeModify_modified(oEvent)
Dim oRange, oRange2, DataArray, i As Long
oRange=oEvent.Source
DataArray=oRange.DataArray
oRange2=ThisComponent.Sheets(0).getCellRangeByName(DatesAddress)
For i=0 To Ubound(DataArray)
If DataArrayOld(i)(0)<>DataArray(i)(0) Then
oRange2.getCellByPosition(0,i).Value=Cdbl(Now())
End If
There is something wrong with this solution. It does not support range expansion. You can not insert new cells.
Yes, of course, it is assumed that cells will not be added and removed in the tracked range.
To track resizable ranges (for example, named ranges), the above approach should be modified.
It’s disabling, because the table becomes practically unusable.
If we sort, delete, add… If a line changes number, the function should be inhibited. But how to do it ?