How do I code (VBA) fixed date entry upon opening workbook?

[LittleBloke]

Hello,

Firstly, apologies - I am completely new to this so please forgive possible stupidity!

I am using an Invoice Template in Excel 2007 and would like to know how to code it so, when I open the Template, the current date is automatically entered. However, I do not want to use

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

as this updates whenever the workbook is subsequently opened, say, a week later. I also do not wish to use the "Ctrl" and ";" to hardcode it, as I shall be giving this Template to another person for the actual invoicing and am looking to have as much of this automated as possible.

For reference, the WorkBook is called "Invoice_A4_Template.xls", the WorkSheet is named "Invoice" and the cell reference is "I4".

Apologies as I realise some threads on this have been created, and I have genuinely tried reading/understanding them, but I'm still struggling so thought I'd ask personally.

Many thanks for taking the time to read this thread.

[shg]

Welcome to the forum.

In the ThisWorkbook module,

Please Login or Register  to view this content.

The user must enable macros for this to work.

[MarvinP]

Hi LittleBloke and welcome to the forum,

You sure have a lot of limits on what we can and can't do for this problem. How about if the cell in I4 is blank the stick the current date in it and don't do anything if it isn't blank? Would that work for you?

Try this code behind the "ThisWorkbook" module

Please Login or Register  to view this content.

The first time you open this template (if Invoices range I4 is blank) it will put in the current date. After you save the workbook and open it again it won't change.

[LittleBloke]

Oh my god, that's so simple!

Thank you - will this mean that if, for example, I opened the Workbook today, "Saved As" and then re-opened the "Saved As" file tomorrow, that it will still show todays date and not tomorrow's? Because that's what I would need.

Thank you though, it has resolved the initial problem.

[MarvinP]

Hey LittleBloke,

Don't trust what I tell you... Open the file and see todays date. Change that sucker in cell I4 and save the file again. Now Open it again and see what is there.

Hope that is what you wanted.

[LittleBloke]

Try this code behind the "ThisWorkbook" module

Please Login or Register  to view this content.

The first time you open this template (if Invoices range I4 is blank) it will put in the current date. After you save the workbook and open it again it won't change.

Sorry, didn't see this reply before answering the other person.

This does work, unfortunately it does not work with the code I have when I increment the invoice number.

Should I create a new thread asking how to put the 2 codes together so they both work as I want them to?

[MarvinP]

Hi,

What code do you have that updates your invoice numbers? Why doesn't this work with that? If you show my the other code and tell me what you want, I think it might go in this tread.

It is best to attach a workbook so I can see it all. You can do that by clicking on "Go Advanced" and then the Paper Clip Icon above the Advanced Message Area.

[LittleBloke]

Hi,

What code do you have that updates your invoice numbers? Why doesn't this work with that? If you show my the other code and tell me what you want, I think it might go in this tread.

Hi MarvinP,

This is code that I now use:

Please Login or Register  to view this content.

I've attached the workbook (well, a copy of it, with personal/private data removed).

I want to be able to increment the invoice number, as well as have the invoice date automatically appear once workbook has been opened. However, when this happens, I need to save the document - filename would be the invoice number.

If I have the date aspect working, the invoice number would work but I cannot save the Template with the incremented invoice number as it would contain a date in the Cell ("I4") - that appears to negate having the code for the Date? If I save the Template before entering any other info, there appears to be no point in having the Date automatically entered?

Is it something really obvious like the fact I've got the code for the date written in before the code for incrementing the invoice number? I wouldn't be surprised

Either way, thank you very much for the help!! Very much appreciated.

[MarvinP]

OK LittleBloke,

I've always wanted to do something like this. Here is the new code behind ThisWorkbook.

Please Login or Register  to view this content.

So here is the plan.
1. Create a Named Range called InvoiceNumber that has a comment of 10001 in the Template.
2. When the template is opened it grabs this number and uses it for a filename.
3. The number is immediately incremented (add 1 to it) in the Named Range comment
4. Then I save the template - the only thing changed is the InvoiceNumber range comment
5. Then I save the File As the invoice number and plug in the date and invoice number.

I think this is what you want. The semi-problem is that you can't really open and look at the template as it closes itself after adding 1 to the number.
If you need to reset the counter you will need to save one of the Invoices with I4 and I5 blank and reset the Named Range of InvoiceNumber Comment to your starting number.

Let me know if any of the above makes sense. Here is the new Template starting Invoice Number of 10001.

I wonder if anybody else has done this kind of thing in a much easier way?

[MarvinP]

Hi,
After doing some mental gymnastics, I began to wonder what would happen if you opened one of your invoices that was half completed. I have no idea!!

If you ever read this or try it and find it somewhat cool , tell me if it works. I think I can fix the invoice sheets from doing bad things. I just need to know someone cares

[LittleBloke]

Hi MarvinP,

I've tried the new code that you supplied, but I got an error message when opening the workbook, and as I didn't realise I should have pressed on "Debug" (which I've since been told), I don't know exactly which part didn't work.

I've used the previous code though and have just decided to, once workbook opened, remove the date, save the template, "undo" the date removal and then ultimately save as! I know it's long-winded but it's working and not taking up much time/effort, so I'm going with that for now.

Thanks for all the help