Adding Date/Time to Footer that actually updates based on actual date & time

mcomp72

Board Regular
For four of the sheets in my workbook, I want to give the user the option to add the current Date & Time in the footer of the sheets, so when they are printed, the Date & Time appear based on the moment the sheets are printed. I have set up the way to turn this option on or off via a UserForm. In the UserForm, the user either checks or unchecks a box, depending on if they do or don't want this info to appear in the footer.

That part works... sort of. The problem is that the Date & Time that appears in the footer is not dynamic, meaning what shows up in the footer is the Date & Time that the user checked the box on the UserForm, NOT the date & time when the sheets are printed. So if they check the box on 10/1, but then print the sheets on 10/3, the date that shows up in the footer is 10/1.

This is the line of code that is executed on the UserForm. ('ws' is set to the particular sheet name that I am dealing with at the moment.)

Code:
ws.PageSetup.RightFooter = "Printed on " & Format(Now(), "m/dd/yyyy") & " @ " & Format(Now(), "h:mm AM/PM")
What I want is for the Date & Time to update each time the user prints one of the sheets. If I insert the Date & Time inside the footer manually, then it works. But I can't figure out how to make it do that via VBA code. Is that possible?
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top