Have AutoDate On, Then Off

[Michale32086]

I use EXCEL 2007 to create work orders and invoices for a small computer shop..

I have a template that I work from that has a cell with an auto-date feature. This saves me time by not having to insert the date all the time.

When I fill out an invoice or work order, I save the template document as a separate document that I can later bring back up and refer to..

Unfortunately, when I open the document, the date it was created (as shown in the Auto Date cell) is changed to the current date.

What I want to do is to keep the auto-date in the template but, when I save it, have the auto-date turned off so that upon subsequent viewings, the date stays the same.

Is this possible??

I should also note that as near as I can tell, the original "template" is not a real template in the EXCEL sense of the word. IE not saved as an .xlt file, but rather just an .xls file that I call a template..

I also have access to EXCEL 2010 if that could do for me what I need.

Thanx.

Michale

 

[Glory]

http://msdn.microsoft.com/en-us/library/aa220840(v=office.11).aspx

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)
 
a = Range("YourAutoDateCell")
 
Range("YourAutoDateCell") = a
 
End Sub

I've never worked with this event before. It's only available as of Excel '03. This code might or might not deliver what you're after (ie, it might replace the formula in the cell with the actual value of the formula, or it might just replace the formula with the formula).

Try it out and come back.

 

[jasonb75]

Welcome to the board!

Is the date generated by a formula in the cell, or by VBA code?

edit: Glory, maybe a way to avoid any doubt would be

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)
With Range("YourAutoDateCell")
    .Value = .Value
End With 
End Sub

 

[Michale32086]

Thanx for the quick replies..

The Auto Date is a formula IE =TODAY()

After posting, I did do a search and found references to that VB code you speak of...

But I am not sure how to incorporate it into my template.

Thanx again to both of ya.. :D


Michale

 

[jasonb75]

But I am not sure how to incorporate it into my template.

Michale

From your template workbook

Press Alt + F11

In the directory tree of the editor window, double click "ThisWorkbook"

Copy / paste the code into the right hand pane of the editor window.

Close the editor and save the template workbook.

 

[Michale32086]

Danke... :D

Got it...

Is there any way to test it, other than to save the document and then wait til tomorrow to open it? :D


Thanx again...


Michale

 

[jasonb75]

There is, but think we need to review the process first.

I just went along with the solution Glory suggested, but just thought of something we both overlooked.

You had to save the template to store the process code in the workbook, which means the code executed "beforesave" as it's meant to, so now the date in the template will not update

If I'm right then the formula will now be missing from the cell, it will just have todays date in it.

Re-enter the formula, then change the code to.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
    If ThisWorkbook.Name = "Template.xls" Then Exit Sub
        With Range("YourAutoDateCell")
            .Value = .Value
        End With
End Sub

Check that the filename is correct and save the template again.

The template should hold the formula in the cell after you save, but any new file created with "Save As" will have the date not the formula.

 

[Michale32086]

OK, I'll make that change...

I am at home now and the files are at my shop.. Making changes via remote access is kinda a pain, so I will try it when I get to my shop...

As far as older files saved prior to this change, I am assuming there is nothing that can be done to those so that they will retain the date they were created....

Thanx again for the assist.

Michale

 

[jasonb75]

It could be done by executing the code line

Range("yourdaterange") = Int(CreateObject("scripting.filesystemobject"). _
      getfile(ActiveWorkbook.Name).datecreated)

on each of them.

It could be possible to automate the process, if for example the files are all located within the same folder, but that is not something I'm familair with, maybe Glory, or another user could advise there?

 

[Michale32086]

OK, I am back at my shop...

I entered the new code as thus:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If ThisWorkbook.Name = "WORK-TEMPLATE.xlt" Then Exit Sub
With Range("E3")
.Value = .Value
End With
End Sub

I confirmed that the name and cell number are correct...

I then saved the document as DateTest2.xls...

When I opened the DateTest2.xls file and it still had the date formula in the cell rather than the plain date..

Now, I know about VBA as much as a walnut, but I think the problem is that the name of the document changes when I do a SAVE AS..

My usual naming convention is to save the documents as WORK-CustomerName.xls

Is that going to work with what you have in mind here?

Thanx again...

Michale