Invoice Templates in Excel

[Guest]

I am using an invoice template - based on excel. The date field automatically
updates to 'today' when I open the template. When I re-open saved invoices it
does the same, which means I lose the 'correct' invoice date. How can I stop
the automatic updating?

Thanks

 

[JulieD]

Hi Sue

did you create the template? or is it one off the MS site - if so, if you
could let us know which one, we'll be able to give you specific instructions
on how to stop this behaviour. If you created it, or don't know where it
came from, if you could let us know the cell reference of "today" and
whether a code / vba solution is acceptable to you.

Cheers
JulieD

 

[Guest]

Hi Julie

I 'inherited' the invoice template - but I believe it was created from a
standard MS office one - it's file path is office\library\invdb.xls. Does
that help at all?

Thanks for your help so far.

Sue@Yes

 

[JulieD]

Hi Sue

mmm, okay, what's happening is that the current date is being put in by a
formula
either
=NOW()
or
=TODAY()
and being a formula / function these will update when you open the workbook,
as you have found. Basically, IMHO there's three approaches you can take
to this situation:
1) delete the formula out of the date field and type the date when you raise
an invoice - using the short cut key combination of control & ; makes this
relatively fast
2) when you've created the formula, click in the cell and copy the date,
then, stay in the cell and choose edit / paste special - values
and this will change the formula into a value
3) write some code so that when you close the workbook it does #2 for you.

if you would like a code solution, be aware you will need to change your
security settings to medium (low is never recommened) and when you open the
template / workbooks in future you'll be prompted to enable macros, which
you'll need to do to for option 3 to work.

If you want to use option 3, if you let me know the name of the worksheet
and the cell reference of the date, i'll (or maybe someone else) will help
out with the code you need to use.

Cheers
JulieD

 

[Gord Dibben]

Julie/Sue

If it is based upon the INVOICE.XLT provided by Spreadsheet Solutions in a
version older than Excel 2002, it will have a macro that places the date into
the cell.

Sub Specific_AutoStart()
Range("data1").Value = Date
End Sub

"data1" is the the named cell(L12) where the date is entered.

Do you get the macro warning when opening?

If so, you have the older version.

The VBA project is passworded with "chzdood"(no quotes).

Go in and rem out the code by placing an apostrophe before each of the 3
lines.

If no macro warning, it would be the newer non-macro version and Julie's
formula advice would most likely be correct, although the newer version(Sales
Invoice) I have with 2002 and 2003 does not have date inserted upon opening.

i.e. no =NOW() or =TODAY() in that cell.


Gord Dibben Excel MVP

 

[JulieD]

thanks Gord

Julie/Sue

If it is based upon the INVOICE.XLT provided by Spreadsheet Solutions in a
version older than Excel 2002, it will have a macro that places the date
into
the cell.

Sub Specific_AutoStart()
Range("data1").Value = Date
End Sub

"data1" is the the named cell(L12) where the date is entered.

Do you get the macro warning when opening?

If so, you have the older version.

The VBA project is passworded with "chzdood"(no quotes).

Go in and rem out the code by placing an apostrophe before each of the 3
lines.

If no macro warning, it would be the newer non-macro version and Julie's
formula advice would most likely be correct, although the newer
version(Sales
Invoice) I have with 2002 and 2003 does not have date inserted upon
opening.

i.e. no =NOW() or =TODAY() in that cell.


Gord Dibben Excel MVP

 

[Guest]

Thanks both for your suggestions.

Have tried to follow your instructions but it still doesn't seem to get rid
of the problem.

Sue

 

[Gord Dibben]

Sue

If data in the invoice is not sensitive, send the workbook to me, not the news
group.

Replace the DOT and AT with appropriate symbols.

I'll have a look at it.


Gord