Help Printing Work Instruction Documents

[charllie]

Hi All

I am looking for guidance and and help regards an issue i have come across. I am hoping that some of you out there have already experienced this and found a work around.

I have some Excel work instruction documents which are password protected and read only. These are available in the cloud throughout the work site and accessible by all staff on their computers. The idea is for better document control. The staff can read and print these documents as required. There are approx. 200 excel documents

What i want to do is; each time a member of staff prints the document a date and time stamp is entered. I then do not want them asked any questions regards saving changes etc when they close the document. Ideally, i would like it to close with no changes made (remove date stamp).

Do any of you have something similar in place and would like to share? Am i approaching this in the right way with over 200 documents? If VBA is required then your help would be very much appreciated.

I also have some documents in Word so will need to look at this also.

Any help with the above will be appreciated.

Thanks

Charllie

 

[6StringJazzer]

IIUC you want the timestamp to appear on the printed document, but you do not want it saved with the document.

How are the users accessing the files? Do they download it from a shared repository? Do they open it directly from that repository, like SharePoint?

One easy way to do this is use the function =NOW() which will show the current date/time, and be refreshed whenever the file is opened and whenever a calculation is performed. This will not be updated at the moment when printing occurs, so it may not be exactly what you want, but it is the easiest thing to implement. To prevent the user from saving any changes, File > Save As > Browse > Tools > General Options > Read-Only Recommended, and add a password to Modify, then save.

If you use VBA you will have to modify each file and save as .xlsm. Also, the user would have to allow macros to run, which you can't control. However, macros will not work if the user is using a web-based version of Excel.

In the ThisWorkbook module add the following code. The first one will add the date/timestamp when the user prints. Change Sheet1 and A2 to desired sheet and cell. The second one sets the state of the file to Saved when the user closes it, so it will think the file is already saved. Note that the date/timestamp will not be saved, nor any other changes that the user may have made.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Worksheets("Sheet1").Range("A1") = Now
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ThisWorkbook.Saved = True
End Sub

No matter what method you use, you will have to implement in each file, 200 times.

Word has a different model. I could not find a handler for the Print event. That would probably get complicated.

 

[charllie]

IIUC you want the timestamp to appear on the printed document, but you do not want it saved with the document.

How are the users accessing the files? Do they download it from a shared repository? Do they open it directly from that repository, like SharePoint?

One easy way to do this is use the function =NOW() which will show the current date/time, and be refreshed whenever the file is opened and whenever a calculation is performed. This will not be updated at the moment when printing occurs, so it may not be exactly what you want, but it is the easiest thing to implement. To prevent the user from saving any changes, File > Save As > Browse > Tools > General Options > Read-Only Recommended, and add a password to Modify, then save.

If you use VBA you will have to modify each file and save as .xlsm. Also, the user would have to allow macros to run, which you can't control. However, macros will not work if the user is using a web-based version of Excel.

In the ThisWorkbook module add the following code. The first one will add the date/timestamp when the user prints. Change Sheet1 and A2 to desired sheet and cell. The second one sets the state of the file to Saved when the user closes it, so it will think the file is already saved. Note that the date/timestamp will not be saved, nor any other changes that the user may have made.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Worksheets("Sheet1").Range("A1") = Now
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ThisWorkbook.Saved = True
End Sub

No matter what method you use, you will have to implement in each file, 200 times.

Word has a different model. I could not find a handler for the Print event. That would probably get complicated.

Hi Jeff

Thank you for the reply and suggestions.

The files will be accessed like you said where they will be opened directly from the repository, SharePoint in this case.

The files are currently set up as read only so your suggestion to use the function =NOW() seems ideal and as you say, the easiest to implement. I will test this out and let you know how i get on.

Thank you for your help, i really appreciate it.

Charllie

 

[6StringJazzer]

You are very welcome! Your users will harpoon my idea if they open the file and leave it open for a week before they print it. But that's not usually what people really do if it's a read-only form.