user-defined function for last saved date/time not updating

Patrick, my users won't know to press F9....is there any way to force a recalculation at close?

Patrick:
I just tried F9, and that doesn't update my spreadsheet, either....??

nbozzy,

Please post a sample file.

In any event, forcing a recalc at close seems odd, as your users will never get a chance to see it.  You could try adding this to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    With ThisWorkbook
        .Save
        Application.Calculate
        .Save
    End With
    
End Sub

Select allOpen in new window

Patrick

Then is there a way to force recalculation after the user saves? Here is file...
Staffing-Calendar-Input.xls

This thing is stuck! I make your change above, and time STILL shows my original save.....??
Staffing-Calendar-Input.xls

nbozzy,

Why did you disable events in your Workbook_Open event?!?

Patrick

oh, crap! I never enabled them!!!
However, after adding the enableEvents=True line, then saving and closing the file, there is no difference when I re-open. Save time still doesn't change.

Your problem is that you had this line in your Workbook_Open event:

    Application.EnableEvents = False

Select allOpen in new window

Since you disabled events, Before_Save is not firing.

1) Remove that line from Workbook_Open

2) In the VB Editor, hit Ctrl+G to get the Immediate window, and in it type Application.EnableEvents=True and then hit Enter

The BeforeSave event will now fire and get your formula to update as expected.

And also be sure that you have macros enabled :)

I do have macros enabled, but this is too weird. Still won't update. Tell me if it updates for you....
Staffing-Calendar-Input.xls

It updates for me

Ok, I'm clueless. I rebooted my PC, did a SaveAs with a new name, verified macros are enabled, and that time STILL doesn't update. What should I do??

I've asked some other Experts to drop in and have a look.

Works for me too, Nancy.

hmmmmm.  Put a Break point on the ThisWorkbook.Save statement in the Workbook_BeforeSave event.  Hit save and advise that the breakpoint is hit and macro paused, or not.

Dave

That's what I was thinking, Thomas - that the event is not even firing.

Dave

Thanks, everyone. The BeforeSave event was firing twice (?) and the Function never ran. For some reason, I commented out the line "ThisWorkbook.Save" in BeforeSave() and now it runs correctly.

I liked the debugging "msgbox" approach that nutsch and TommyS suggested!

Thanks to all!

It looks like Application.Volatile is not working as I would expect it to work.

This will work:

Public Function Doc_Saved(ByVal Dummy As Variant) As Date
    'returns date & time last saved
    Application.Volatile
    Doc_Saved = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

And then pass a volatile function to the UDF:

=Doc_Saved(NOW())

Kevin

Using break-points are also a solution / alternative to putting messageboxes in.

Enjoy

Dave

Oops missed that one. ThisWorkbook.Save actually calls the BeforeSave event so it must have been confusing it. As long as you don't set Cancel to True, then the workbook will be automatically saved after the BeforeSave function runs.

LOOOOOOOOOOP??

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
application.enableevents=false
    ThisWorkbook.Save
    Application.Calculate
application.enableevents=true
End Sub

Select allOpen in new window

When Patrick calls, everybody answers it seems.

its like we're an army!

lol

Dave

When anyone posts in the Regulars thread we help.

You're all fantastic. I love this website!!! Thanks to all.

If you're using beforesave, why bother with the UDF at all? Just write the time to the cell in the event code.

Good point, rorya! The process evolved that way because I needed to force a recalculate....thanks for the slap back to reality!