How do you get functions to update

Posted by Bob Elliott on June 21, 2001 9:59 AM

I am an excel newbie.

I am revising a program at work and I don't know how to get certain functions to update. There are two functions in particular.

The first is in a cell. The line is NOW() and it is supposed to give the current date and time. The thing is it only updates when the spreadsheet calcualtes. How to you get it to update real-time?

I wrote a macro that will write the filename to a cell. Here is the code:

Sub file_name()
Dim this_file

Application.Volatile (True)
this_file = ActiveWorkbook.Name
Sheets("Spacecraft Information").Select
Range("B63").Select
ActiveCell.FormulaR1C1 = this_file

End Sub

The thing is if I save the file to a different name the cell will only update when I run the macro. how do I get it to update automatically?

Thanks for your help.

Bob Elliott

Posted by Damon Ostrander on June 21, 2001 2:56 PM

Hi Bob,

Excel only updates formulas on a sheet
calculation, whether triggered manually or
by a worksheet change event. I assume from
your problem description that you do not want
the time to update continuously, or every second,
but just when you do a file save. Is this right?

If this is what you want to do, just use a
BeforeSave event and write the time to the
cell where you currently have the =NOW() formula
each time a save occurs using VBA. For example,
if the =NOW() is in cell B5, then just put the
line of code

[B5] = Now

in the ThisWorkbook BeforeSave event code block.

Happy computing.

Damon