Function for last modified date of cell.

[flpgdt]

Hi,

I was looking into this thread, which is pretty close to what I want, but still not quite. I kinda managed to do some modifications and turn it into a function but just don't know enough of these events handles.

What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.

E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"

-- Make an update:

A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"

I'm not quite a super user in Excel, but this function would be very very useful for me.
Could someone land me hand?

Thanks!

f.

ps. I'm using Office 2010

 

[Eric W]

Where are your dates now? This was just an example showing the concept. You only need to put this macro ONE time in your worksheet, even if you have 100 dates. But you do need to come up with a way to relate the price cells with the date changed cells. Or even a range, you can have 1 date for a dozen prices.

Then your idea of using Conditional Formatting to look for old dates would work very well.

 

[bigmyk2k]

For my spreadsheet, the prices are in column C, and the LastModified function is in Column I.

I entered the Module as written since my lookup data was already in column c.

Should i change "c As Range" to something else?

 

[Eric W]

No need. The second line determines where to put the date. It's done as an offset from the cell that you change. So if your price column is C, and the LastModified column is I, that's an offset of 6. Change the macro like so:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C1:C100")) Is Nothing Then Exit Sub  ' C1:C100 is the price range
    Target.Offset(0, 6) = Now()                                      ' This will put the current date 6 columns over
    
End Sub

 

[bigmyk2k]

Ha, I figured that out about the same time you were writing this response. It looks like I still need the original code in order to avoid a #NAME error, is that correct?

This is the original code I'm referring to:
Public Function Lastmodified(c As Range)

Lastmodified = Now()

End Function

 

[bigmyk2k]

It looks like the combo of those two modules is the trick. But I still have no date earlier than when I put in the macro, is there any way to get around that to the date the macro was originally entered?
If not, no big deal, you've been a great help as it is!

 

[Eric W]

If you keep the formulas in column I, then you would still need to keep the original code. The new code will gradually overwrite the formulas as time goes by. What you may want to do is copy column I, then do a Paste Special --> Values back to column I. That will remove the Lastmodified function from every cell in column I, and you can then delete the Lastmodified code from VBA. That will also mean that the dates in column I will stop changing, unless you change a price in column C.

Note that you can manually change the dates in column I if you want. If you have a date that is today's date, and you know the last change was last week, you can go ahead and change it. This might be a bit tedious if you have a lot of changes like that, but once it's done, the new macro will keep things up to date. You can also pick a date, and copy it to every cell in your column I range. Then the new macro will put new dates in as they occur.

Let me know what you end up doing.

 

[bigmyk2k]

If you keep the formulas in column I, then you would still need to keep the original code. The new code will gradually overwrite the formulas as time goes by. What you may want to do is copy column I, then do a Paste Special --> Values back to column I. That will remove the Lastmodified function from every cell in column I, and you can then delete the Lastmodified code from VBA. That will also mean that the dates in column I will stop changing, unless you change a price in column C.

Let me know what you end up doing.

Arrgh. I did the copy-paste, so there are only values in Column I, then deleted the LastModified module.
But, now when I enter a new value in column C, nothing changes in I. (I increased the range to 10,000 rows).

I'm sorry to be such a bother. I'm not much for VBA (yet) but from what I can see in the code this should work.

Here is what I have entered currently:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Intersect(Target, Range("C1:C10000")) Is Nothing Then Exit Sub  ' C1:C10000 is the price range
    Target.Offset(0, 6) = Now()                                      ' This will put the current date 6 columns over
    
End Sub

 

[Eric W]

As you said, it should work. A few things to try: make sure that the macro is on the right VBA sheet, with the same name as the sheet your data is on. From within the VBA editor, go to the Immediate window. It should be on the bottom of the window. If it's not there, press Ctrl-G. Then type

Application.EnableEvents=True

in that window, and test again.

Finally, if it still doesn't work, go to the code, put a breakpoint on the first line by putting the cursor on that line and pressing F9. Then test again. That will at least tell you if the code is getting executed. If it stops there, then type:

Print target.address

in the Immediate window again.

Let me know what happens.

 

[bigmyk2k]

Did the EnableEvents, tested: Didn't change anything
Did the breakpoint, tested: nothing happened (no errors, no update in Column I, etc.)
Print target.address: "Run-time error '424': Object Required"

 

[Eric W]

Since it didn't stop at the breakpoint, and you have Events enabled, about the only thing I can think of is that the macro is on the wrong VBA sheet. It should be on a sheet with the same name as the Excel sheet (Sheet1 for example). It should not be on a module sheet (Module1). The function you removed was probably on a module sheet, but the new macro should not.