ModDate - refresh macro so that =ModDate() in cell refreshes without needing to save?

Hi all,

I greatly appreciate any and all help you can provide I am sure this is a simple thing to fix, but I am super rusty with Excel macros.

I have a spreadsheet for our rebates which will be changed regularly during the approval process.

I have entered this into the VBA macros of this Excel spreadsheet:

Public Function ModDate()
ModDate = Format(FileDateTime(ThisWorkbook.FullName), "m/d/yy h:mm ampm")
End Function

I have then entered the following into a cell on the worksheet:

=ModDate()


The issue is that the cell which displays the modified date and time does not refresh when changes are made to the worksheet until it is saved. I know that saving it is necessary in order to have the correct modified date and time in the properties of the document, but is there any way to have the ModDate refreshed as soon as any changes are being made to the worksheet?


Thank you again, in advance, for all your help!

Use this

Please Login or Register  to view this content.

Change the range to the cell being changed.

Thank you!

Hmmm... still doesn't work, until I click on the cell being changed and enter, as if to "refresh" the cell. I also discovered that I have the problem of the modified date and time changing when click on this cell, even when I have not changed anything - but only when I either first open the workbook, or save it. So it is really logging saved and/or opening time, as opposed to modified time.

Any help you can give me is greatly appreciated!!! Thank you!

Let me just note that I am okay with the last modified date being the last saved date, as I suppose I will have to save any changes anyway.. but is it possible to not have it show modified if I simply open the file? And if possible, can the cell refresh with the accurate modified date before saving? (Though if this is not possible, that is fine... thought it was worth asking, regardless!)

Thanks again!

What you can do is copy the original time(on file open) and paste it as value not formula in a different cell, then you can find a different event that will trigger the cell refreshing, and keep track of the different times in a control sheet.