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

 

[bigmyk2k]

Genius!!!
That was the trick!
Thanks for all your help!

 

[Eric W]

I'm happy it works for you!

 

[cason_]

Eric,

I saw this post and am trying to replicate what bigmyk2k did on his excel sheet.

I have put the code under the Microsoft Excel Objects in Sheet1 like you said, but when I go to run the code there are no macros to run. I'm not sure how to get the Module code to run.

 

[cason_]

Actually I think I just figured out what I was doing wrong! Now when I edit the cell the other cell displays the date.

Do you know how I would be able to modify this program so that this program would only display the date when the color of the cell is changed? Normally the data that is in my workbook stays the same, as they are lists of computers, and I change the color of the cells as a reminder as to which computers I have updated or not.

 

[svkroy]

This thread is quite old, your message suddenly popped up in my inbox...... I think you would get some more help, if you open a new thread, if that still does not help, pm me i would modify the code for you. svkroy at the rate yahoo dot com.

 

[Eric W]

svkroy is right, it's generally better to open a new thread instead of adding to a very old one. More people will see it. It's OK to PM someone if you think they have the requisite expertise. I generally unsubscribe from a thread once it appears to be resolved.


As far as your question, Excel does not have any event handler that recognizes a format change. So if you change the background color of a cell, there's no way we'd know about it. There are some other options. Best I can think of offhand would be to use the DoubleClick handler. You could double click on a given cell, and if it's in the right range, it would toggle the background between green, red, and none. It could also change the date in an adjacent cell.

If that's of interest, let me know and give me the particulars, and I'll write it up.

 

[Eric W]

Hi Luke, and welcome to the Board!

Just as a point of information, it's probably better to open a new thread with a question like this. If I'm not available for some reason, then there are several other people on the board here who can help. Also, other people could benefit from seeing the question. And you have to have a certain number of posts before you can receive PMs. I hope you found this thread from the tagging. @Purkis

For your question, the main issue is that there can only be one WorksheetChange event handler per sheet. But you can make it handle multiple ranges. In your case, the change can be as simple as:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("DI6:DI15000,DK6:DK5000")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Now()
    
End Sub

This works because the ranges are non-overlapping, and the offset for both columns is 1. The following version might still be a bit "safer":

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("DI6:DI15000")) Is Nothing Then
        Target.Offset(0, 1) = Now()
    End If
    
    If Not Intersect(Target, Range("DK6:DK15000")) Is Nothing Then
        Target.Offset(0, 1) = Now()
    End If
    
    Application.EnableEvents = True
    
End Sub

The EnableEvents = False/True is there to prevent a loop, which can happen if the Change event changes a cell in the acceptable range. The two ranges are done separately, to allow different offsets if you want, also to show how to add additional ranges.

Hope this helps!

-Eric

 

[Purkis]

Hi Eric,

Perfect! Exactly what I was looking for and worked first time - Thanks so much for the help and noted regards opening a new thread.

Again, thanks for the help, life saver!!!!

Luke

 

[pluginguin]

Hi there...

I found this thread, because I also need this function/code.
I placed in on the sheet code and it is firing when I update a cell in the reference column.
But it doesn't change any time.

I know what the reason is.... the reference column is a formula.

Here's the story.
It's about a price sheet, just as Bigmyk2k
I have sheet A that contains purchasing prices.
Sheet B calculates the sales price. If I change a purchasing price the sales price automatically recalculates.
The sales price column (reference column) contains the formula that does that.
From this sheet B, we create fixed value copies of the sales prices for import into a webshop.
The price formula must remain for obvious reasons... but I want to limit the size of the import files by only copying those sales prices which have been updated, because there is a new purchasing price.

Your formula however, sees no change in the reference column, because the formula itself doesn't change.... only the result of the formula does.

So my question is this:
Can your code be modified so that it displays a date when the result of the formula changes?

 

[AndyMcC]

The problem with that UDF is that it updates every time the sheet recalculates. And you have found the Ctrl-Z won't undo VBA changes. What it sounds like is that you're looking for a solution that only updates a date when a specific cell is changed. Try putting this code on the VBA sheet that you're changing:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C1:C100")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Now()
   
End Sub

This monitors your worksheet, and if any cell in the range C1:C100 is changed, then the date/time is stored in the column (D) next to it. The date will never change again, unless you change that cell again. And if you want, you can manually put in your own date in D.

Let me know if that's useful.

I have two columns, C and E, to which I want to apply this method. I have tried just duplicating the code

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C5:C32")) Is Nothing Then Exit Sub
Target.Offset(0, 1) = Now()
If Intersect(Target, Range("E5:E32")) Is Nothing Then Exit Sub
Target.Offset(0, 1) = Now()


End Sub

And whilst it works for column C it doesn't for column E.

I'm obviously missing something?