How to insert the date the workbook was last printed

[Chuck1350]

Hi there Folks!
I came up with the problem of inserting the date the workbook was last printed, as seen on File -> Properties -> Statistics, because I needed to have it in the document sheet. I've wandered the net for an answer but i didn´t find one. Thanks for helping!

 

[Smitty]

Welcome to the Board!

See if this suits you. It will place the date in Sheet1, cell A2 before the workbook is printed:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    Sheets("Sheet1").Range("A2") = <SPAN style="color:#00007F">Date</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[Tom Urtis]

You might want to know when the workbook was last printed by someone other than you, such as when you open the workbook or do some kind of investigation or audit. Sometimes, people have no need for knowing when they printed something from the built-in property because they can see for themselves that they are printing right then and there, and they can place that info on a sheet header or footer.

If you don't want to have to print the workbook yourself, which would skew the audit or whatever purpose you may have, then place the following UDF in a standard VBA module.

Function Printed()
Printed = "Last printed: " & Format(ThisWorkbook.BuiltinDocumentProperties.Item(10), "MM/DD/YY")
End Function



To return the last printed date, enter this formula in a worksheet cell:
=Printed()

 

[kidkl]

Welcome to the Board!

See if this suits you. It will place the date in Sheet1, cell A2 before the workbook is printed:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
Sheets("Sheet1").Range("A2") = <SPAN style="color:#00007F">Date</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

Hello penny..

just wondering..

can we set the date to a format like : dd mmm yyyy


thanks

 

[Smitty]

Sure:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    Sheets("Sheet1").Range("A2") = Format(Date, "dd/mmm/yyyy")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

 

[kidkl]

Sure:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
Sheets("Sheet1").Range("A2") = Format(Date, "dd/mmm/yyyy")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

Thanks penny

 

[Ekim]

Sheets("Sheet1").Range("A2") = Format(Date, "dd/mmm/yyyy")

What happens if you print the file in the morning and again in the afternoon on the same day? The date in A2 will not change.

Suggestion – insert the date and the time:
Sheets("Sheet1").Range("A2") = Format(Date, "dd/mmm/yyyy hh:mm AM/PM")

Regards,

Mike

 

[Smitty]

What happens if you print the file in the morning and again in the afternoon on the same day? The date in A2 will not change.

Good point Mike.

I also like Tom's solution better as my code will time stamp even if a user cancels the print job.

Smitty

 

[kidkl]

Sheets("Sheet1").Range("A2") = Format(Date, "dd/mmm/yyyy")

What happens if you print the file in the morning and again in the afternoon on the same day? The date in A2 will not change.

Suggestion – insert the date and the time:
Sheets("Sheet1").Range("A2") = Format(Date, "dd/mmm/yyyy hh:mm AM/PM")

Regards,

Mike

yup..you have the point there..thanks