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

 

[jasonb75]

I think the theory is right, just a problem with the order of events.

To test this, if you open the DateTest2 file and save it (Ctrl S) does the formula change to date then?

 

[Michale32086]

Nope, still stays as =TODAY()

Michale

 

[jasonb75]

Ok, maybe try from a different angle, it could be something I'm missing or it's possible that your security is stopping the code from running, either way, if you use this code instead it should work.

It inserts the date for me when testing, and if your security blocks it then it will prompt you for to allow it.

As before, this code goes in the module "ThisWorkbook"

Private Sub Workbook_Open()
MsgBox "Code Permitted"
End Sub

This code goes in the worksheet module (above the "ThisWorkbook" module there should be one with the same name as the worksheet i.e. "Sheet1"

Private Sub WorkSheet_Change(ByVal Target As Range)
Range("yourdaterange") = Int(CreateObject("scripting.filesystemobject"). _
      getfile(ActiveWorkbook.Name).datecreated)
End Sub

All code goes in the Template workbook, Save and close, then re-open.

When you open you should get either a message saying "Code permitted" or a prompt to permit / enable it.

The date in E3 should update when you make a change to one of the cells in the sheet (note that this will be the date that the template was created until you save as a new file then make a change).

 

[Michale32086]

OK making progress

I put in the code, saved and then closed..

When I re-opened, I got a CODE PERMITTED box, so, so far so good..

However, when I make any change to the document I get a box that says RUNTIME ERROR '53':
File Not Found

I am guessing that your codes has a value that is specific to my document.

Is it permissable to attach the XLT document in this forum??

Michale....

 

[jasonb75]

Found the problem, something I didn't test for

It appears that the code is getting confused trying to get the file information from the auto-recovery backup, not the original, this is the cause of the code failure.

I was hoping that would work, my next theory was to find a way to execute it against existing files if you reopened them, but that will have to be done a different way now.

Trying to solve mutiple tasks in parallel isn't working so I'm going back inside the box with a much simpler solution.

Private Sub Workbook_Open()
Range("E3") = Date
End Sub

Use this code only, remove the other 2.

I'll have another look at the problem with the old file dates later when my tiny mind stops trying to overcomplicate things.

 

[Michale32086]

I know the feeling...

It's like when I strip down a laptop to the bare motherboard to fix an AUDIO problem, only to learn that there was a very small volume dial in the front of the laptop that was turned all the way down..

"Do ya ever get the feeling that we make things harder than they have to be?"
-Mac, NIGHT COURT



Anyways, OK... I put the new code in... I saved it as DateTest3.xls

Closed the WORK-TEMPLATE.xlt and then opened the DateTest3.xls

The date is still in as a formula, not just the plain date..

I am not really worried about the old documents.. For the few times I need to look up and print an old work order, I can just take the date from the WINDOWS EXPLORER Details Listing...

But it would be nice to nip this in the bud from here on out..

Again, I really appreciate the assist...


Michale

 

[jasonb75]

Did you close DateTest3 as well?

I'm not entirely sure that re-opening a document that is already open will trigger the code.

Just to confirm, that code should be in the "ThisWorkbook" module.

 

[Michale32086]

SUCCESS!!!!!

I had put the code in the wrong workbook... :D

Thanx a bunch!! I really appreciate your efforts...

The old files would be a bonus, but this is just awesome!! :D

Thanx again....

Michale

 

[Michale32086]

Looks like I may have jumped the gun...

While it's true that the DateTestx.xls document had the plain date in it, so does the TEMPLATE-WORK.xlt template...

Where's a head banging emoticon when ya need one!


Michale

 

[jasonb75]

Just a small point I didn't think of.

When you "Save As", change the file extention to "xlsx" not "xls"

When you get a warning box about macro free workbooks, answer "Yes".

If you use the xls file extention, the date will continue to change when you open the newly saved book, the same as it did with the formula.

The xls creates an identical copy when saving (including any code) xlsx creates a code free copy.