How to show date printed, updated only when worksheet printed?

[Guest]

I have a timesheet spreadsheet with worksheets for each day. I want the date
that a specific worksheet (e.g., Tuesday) is printed and don't want that date
to change on the worksheet until/unless that specific worksheet is printed
again. Each worksheet could/should have a different print date. Using
header/footer shows the date it is printed, but changes each day. Is there a
way to do this?

 

[CLR]

It would take a little experimenting to see if it worked right, but it seems
like a custom "Before-Print" macro ought to do the job. Or maybe special
"Print" buttons on each sheet to run macros that changes the date before
printing the sheet.

Vaya con Dios,
Chuck, CABGx3

 

[Gord Dibben]

Sam

First off.........save a backup of your workbook.

Then use this User Defined Function

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

'=DOCPROPS("last author")
'or
'=DOCPROPS("last save time")
'or
'DOCPROPS("creation date")
'or
'=DocProps("last print date")

For a list of other properties that may be available(not all are), run this
macro.

Sub props()
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
rw = rw + 1
Next
End Sub


If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula in any cell as shown above.


Gord Dibben Excel MVP

I have a timesheet spreadsheet with worksheets for each day. I want the date
that a specific worksheet (e.g., Tuesday) is printed and don't want that date
to change on the worksheet until/unless that specific worksheet is printed
again. Each worksheet could/should have a different print date. Using
header/footer shows the date it is printed, but changes each day. Is there a
way to do this?

Gord Dibben MS Excel MVP

 

[Guest]

Well, it is a step in the right direction. The last print date appears to
change for ALL worksheets as I print ANY worksheet. e.g., I print worksheet
"Sat" on 7/11 then print "Mon" on 7/13. Print date for "Sat" then appears as
7/13. However, the dates don't change at all until I open the cell,
essentially recalculating. Is this how it is supposed to work?