FileDateTime function - how to make it authomatic?

Hi all,

Have a shared workbook with three worksheets - I am trying to see in a cell the last of them that has been changed (and saved) so for the others to catch up, without waiting the minumum 5 mins allowed in the tabs.

I am using a FileDateTime function to show the last time/date of the file in the shared directory.

It works fine the first time. However, to see the latest updated value I need to go in the cell and retype the function manually (i.e. is not automatic). Is there a way to go around it?

Many thanks

BC

What code are you using?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bondcrash" <[email protected]> wrote
in message news:[email protected]...
>
> Hi all,
>
> Have a shared workbook with three worksheets - I am trying to see in a
> cell the last of them that has been changed (and saved) so for the
> others to catch up, without waiting the minumum 5 mins allowed in the
> tabs.
>
> I am using a FileDateTime function to show the last time/date of the
> file in the shared directory.
>
> It works fine the first time. However, to see the latest updated value
> I need to go in the cell and retype the function manually (i.e. is not
> automatic). Is there a way to go around it?
>
> Many thanks
>
> BC
>
>
> --
> bondcrash
> ------------------------------------------------------------------------
> bondcrash's Profile:
http://www.excelforum.com/member.php...o&userid=20997
> View this thread: http://www.excelforum.com/showthread...hreadid=553997
>

This is the one I am using


Function last_time()
Dim all_saved As Date

all_saved = FileDateTime("\\Your-1af8a1ea42\SharedDocs\control.xls")
last_time = all_saved

End Function


I have tried also reopening the sheet or recalculating but only way to update the right time is to go in the cell manually and re-enter it


Cheers

BC

It won't recalculate automatically, as there is no cell changed when you
save, and the formula doesn't reference anything, so there is nothing to
trigger it.

You could trap the save event and write to the cell.

Or, if you use this code you can force a recalculate with Alt-F9

Function last_time()
Dim all_saved As Date

Application.Volatile
all_saved = FileDateTime(ThisWorkbook.FullName)
last_time = all_saved

End Function



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"bondcrash" <[email protected]> wrote
in message news:[email protected]...
>
> This is the one I am using
>
>
> Function last_time()
> Dim all_saved As Date
>
> all_saved = FileDateTime("\\Your-1af8a1ea42\SharedDocs\control.xls")
> last_time = all_saved
>
> End Function
>
>
> I have tried also reopening the sheet or recalculating but only way to
> update the right time is to go in the cell manually and re-enter it
>
>
> Cheers
>
> BC
>
>
> --
> bondcrash
> ------------------------------------------------------------------------
> bondcrash's Profile:
http://www.excelforum.com/member.php...o&userid=20997
> View this thread: http://www.excelforum.com/showthread...hreadid=553997
>

cheers Bob - missing line... now working function.... :-)