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

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
275
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top