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

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?

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

Yes, there is a way to show the date a specific worksheet is printed and have it remain unchanged until the worksheet is printed again. Here are the steps to achieve this:

1. Select the cell where you want to display the print date. For example, you could select cell A1.
2. Click on the "Formulas" tab in the ribbon and then click on "Define Name" in the "Defined Names" group.
3. In the "New Name" dialog box, enter a name for the print date, such as "PrintDate_Tuesday" if you want to display the print date for the Tuesday worksheet.
4. In the "Refers to" field, enter the formula
   Formula:

   ---

   =IF(Tuesday!$A$1="",NOW(),Tuesday!$A$1) 


   ---

   (replace "Tuesday" with the name of your worksheet).
5. Click "OK" to save the new name.
6. Now, whenever you print the Tuesday worksheet, the current date and time will be displayed in cell A1. If you print the worksheet again, the date and time will not change.
   
   Note: If you want to display the print date in a different cell, simply change the cell reference in the formula. Also, you will need to repeat these steps for each worksheet where you want to display the print date.

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

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



"Sam" wrote in message
...
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?

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

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 InsertModule. 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

On Sat, 30 Sep 2006 07:44:02 -0700, Sam wrote:

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

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

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?

"Gord Dibben" wrote:

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 InsertModule. 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

On Sat, 30 Sep 2006 07:44:02 -0700, Sam wrote:

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