VBA function not refreshing Excel 2016

[maxwelln1]

Morning all,

Long time reader, first time poster, so hello!

I'm having a bit of trouble getting a VBA function to refresh. I would like to have a cell at the top of my excel sheet which shows the last time the file was modified, I am using the following function:

Please Login or Register  to view this content.

However it will not refresh unless I click into the cell and press enter.

Please can you help by suggesting how this might refresh without being clicked on?

Thanks in advance,

Max

[Marc L]

Better is to use a workbook event so paste next code to the ThisWorkbook module and mod the range to suit your need :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Range("A1").Value = Now
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[maxwelln1]

Hello,

Thank you very much for your reply. Unfortunately I'm not sure how to do what you're suggesting. Was 'ThisWorkbook' supposed to be a hyperlink?

This is the first time I'm using VB so might need some baby steps!

Thanks again!

[Roel Jongman]

Hi Maxwelln1

Application.volatile "instructs" excel to also recalculate this function every time the sheet is recalulated.
I think "the problem" is that a workbook is normally recalculated just before it is saved. But the property last save time is not updated yet because that property is set after/ during save process.
you cloud verify this by saving your worksheet and then pressing F9 thought triggers a recalc and should also update your UDF (user defined function)

The solution of Marc L would solve that problem as the cell A1 is updated just before (a split second) the workbook is saved.

[maxwelln1]

Hi Roel,

Thank you for your help. I can confirm that saving and then pressing F9 does update the cell to show the correct time/date.

Oh I figured out what to do with Marc L's code and it worked perfectly! Thank you both for your help

Just to help others (if as new to this as me), I pressed alt-F11, then on the VBA screen clicked Insert -> New Module and pasted this code in:

Please Login or Register  to view this content.

Then, in the Project - VBAProject window on the left there is a folder structure, I went into 'Microsoft Excel Objects' and found the module 'ThisWorkbook' and pasted in Marc L's code:

Please Login or Register  to view this content.

You can change A1 to be any cell of your choice where you want the date to appear. I went to cell A1 and typed in the following:

Please Login or Register  to view this content.

Then format the cell as date or time and you're off to the races! Saving the sheet updates the cells.

Thank you again to both of you above!


EDIT - I've just realised that you may not even need to do the first and last step at all!