Solved

Update Date - On Print?

Posted on 2012-03-19
9
240 Views
Last Modified: 2012-04-04
Experts,

I have a hard coded date on a sheet.
I would like for the date to be updated upon print.
How can I do this?  

Not sure if there is something "canned" inside of Excel for this.  
I am using 2k7.

thank you
0
Comment
Question by:pdvsa
  • 5
  • 4
9 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 37737400
Put this in the workbook code module.  Replace "Sheet1" and "A1" with appropriate sheet name and range.

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

Open in new window

Kyle
0
 

Author Comment

by:pdvsa
ID: 37740379
Hi Kyle, sorry for not getting back earlier.   Was jam packed with work from real job.   Getting back to the question.  If i have a range set in the sheet and within that range there is a cell with a date then i think you are saying the cell with a date will be updated?   Thank you...  Do you happen to know hiw to do this without code?  I ask because i believe i would have to save as a macro enabled file.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37741980
Without macros there is really no way to make sure the update ONLY happens before a print event.  You have several other options though.  You could use a formula like

=Today()

in your cell.  Then just make sure the workbook has recalculated before you print.  Or you could put a date in header/footer.  This will auto-update.

Other than that there aren't many other options.  How exactly are you trying to update the cell?  I'm going on the assumption that you want the current date before the document is printed.  Even if this is not the case we might be able to come up with a formula to suit your needs.

Kyle
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pdvsa
ID: 37802892
Kyle, I forgot about this question.  Sorry.  past few days have been very busy.

Is it true that if I put =Today() then once I open the file the date will be overridden?  If I dont save the file then there will be no changes.  I am looking for no update of the date unless I print.  

Example:  if I have a file with a lable such as "Date Printed" and open the file, the date printed would be changed to the current date if I use Today().

Not sure if I am making sense or you already covered it.  

thank you...
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37802980
pdvsa,
The code provide in my first post will insert static text into the cell.  It will not update, ever, unless you print.  That's what the BeforePrint Event does.  It only fires when you print.  So you can do anything you want to the workbook, the date will not change until you print.  Let me know if you have further questions.

Kyle
0
 

Author Comment

by:pdvsa
ID: 37804108
Hi Kyle, my only issue with vba is that the other users if the file will not know what to do once they get the security message to enable macros.  

I was thinking that there could be fields much like in ms word that you can insert and apply a sort of code within excel without vba.  

Do you know what i am talking about?  

Thank you
0
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 37804449
pdvsa,
I'm not sure what else to tell you.  Here are your options, which I will state again.

1.  VBA BeforePrint event.  Will fire before printing so date will only update before printing.
2.  Formula in worksheet.  Date functions are volatile so they will fire whenever the worksheet is recalculated.  If you want to enter today's date in a non-volatile way do Ctrl+;.  This is static and will not update before printing.
3.  Put a date code in the header of the worksheet.  This will only update when printed.  I think this might be your best bet.  If you need help with this let me know.

Excel does have "fields" like in Word.  In fact it has 17,179,869,184 of them (per worksheet) :-).

All joking aside.  I'm sorry this isn't the answer you were looking for but, like I said, I'm not sure what else to say, these are pretty much your options.  There may be some obscure Excel 4 macro out there but that was way before my time.

Kyle
0
 

Author Closing Comment

by:pdvsa
ID: 37807207
Ok thank you.  It wasnt exactly what I was looking for but I will tell the other users to enable the macros on open or make a trusted area to avoid having to enable them each time you open teh file.  thank you Kyle.  I didnt mean to be difficult.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37807407
No problem.  I'm sorry I wasn't able to come up with a better solution for you.  If you want to take the time, you may want to ask another question and link to this one.  I am by no means the final authority on Excel.  There are plenty of MVP's that frequent this TA.  One of them might be able to give you exactly what you want.  If that happens I will be happy to get a mod to refund your points and withdraw this Q from the knowledge base.

Kyle
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question