progressive invoice with monthly reset

The first part is easy(ish). You need a bit of code in the worksheet BeforeSave event.

To get to this:

1. Open your workbook (and this will be easier if you just have the one workbook open).
2. Press Alt-F11 to open the VB editor
3. In the left-hand pane double click on the "This Workbook" object
4. In the drop-down list at the top left of the main pane select "Workbook"
5. In the right-hand drop down select "BeforeSave"
6. The following code will appear automatically in the main window:

Please Login or Register  to view this content.

7. Edit the code so that it reads.

Please Login or Register  to view this content.

You can adjust this to update whichever cell you want.

The 2nd part is slightly trickier. The count can be reset to 001 easily enough in a new month, but when do you want this to happen? Should it happen the first time you save the sheet on the 1st of the month, or the first time you open it in a new month, or based on some other trigger? Will the sheet be opened or saved more than once a day?

I'd like it to reset upon opening in a new month. As for opening it, sometimes daily or multiple times a day, to not being used for weeks at a time.

OK - is it best to generate the new invoice number on opening or before save?

The code is pretty similar for both and the methodology would be the same as in my previous post, just instead of selecting "BeforeSave" at step 5 select "Open" ...

Please Login or Register  to view this content.

Ok, I used your code and it worked....almost. I've got this saved as a template(forgot that part, as I said, I'm new), and I want that last bit to advance each time I click the template to have it open a new sheet....its on that sheet I need the number to +1....is this still possible?....and let me know if I need to explain this clearer...thank you so much.

Hmmm, I'm not sure - something, somewhere, has to keep track of which number you're up to and if you're creating new sheets then it can't be done in the sheet, as it is with the code I've provided so far.

Originally Posted by royUK

Probably store the number to the registry

Registry, pah! Back when I were a lad we had .INI files and thought ourselves lucky.

Unfortunately, in the 150 years that have passed since then I've never learned how to read and write to the registry

Originally Posted by royUK

Try

Please Login or Register  to view this content.

You can still use an .ini file or even a text file

Would I use this in conjunction with the above bit, in place of? I'm very new, about a week now with excel...looking for a good place to get Tuts as well....also I'm re-stated my question to clarify exactly what I am after....

Originally Posted by CappyLarou

Gonna try this again, I didn't ask my question properly.

I want to make a template that when opened automatically fills a Cell with an invoice number in this format: yy-mm-001 where 001 automatically progresses by +1 but also resets to 001 on the 1st day of each month. How do I do this? I've been using excel for about 1 week now, so please be kind.

I have more I'd like this to do, but want to figure out what I can before asking for more help...thanks

Sorry to leave you hanging on this one - the bit that's missing is I have no idea how to make a template place a new invoice number every time it's used to generate a sheet. Hopefully somebody else round here can fill in for my ignorance.

@Andrew-R = you've already helped me more than I was honestly expecting...if this whole forum is full of folks like you, i'll be a pro in no-time....

I think I've got it (finally).

The attached template will, when you use it as a template for a new workbook, put the invoice number in cell B1 on sheet 1.

Is this the kind of thing you were after?

PS Many thanks to Roy for his expert knowledge on this one. Much appreciated.

Sorry, yes, that was a 2007 file.

Let's try again

now, is it possible to make it so that it doesn't advance unless saved? if so, just give me a heads up what to change in the code and I'll jump all over it...thank you again.

I also want to save it in a specific way, but I already know how to do that...

So the set up you want is:

1. You click on the template to create a new invoice
2. The new invoice shows the old invoice number or no invoice number until it's saved
3. On save the invoice number increments by 1 and is written to the sheet

Is that what you're after?

Originally Posted by Andrew-R

So the set up you want is:

1. You click on the template to create a new invoice
2. The new invoice shows the old invoice number or no invoice number until it's saved
3. On save the invoice number increments by 1 and is written to the sheet

Is that what you're after?

If it can't display the new number, then I would like to see show nothing...but if it shows nothing, can I still point to that cell for the save name?

It can show the new number - that's fine.

OK, this template will show the number it's going to assign, but won't update the counter until the workbook is saved.

For long and complicated reasons it's probably best to only create one new workbook at a time - don't create another one until you've saved the first one.

Originally Posted by Andrew-R

It can show the new number - that's fine.

OK, this template will show the number it's going to assign, but won't update the counter until the workbook is saved.

For long and complicated reasons it's probably best to only create one new workbook at a time - don't create another one until you've saved the first one.

thats the plan...can't thank you enough, gonna put this in my template and see how it goes...

ummm, how do I make it start at 001? it opened up right away at 006 :-)

Run the macro called "InitialiseNumbers"

Tragically, despite complaining for ages about the new tool ribbon in Excel 2007 I can't even remember how to run macros in Excel 2003

Alt+F8...lol...woohoo, I helped someone...LOL

time to go study some tutorials now and learn more...maybe be that excel for dummies book

ok, so is this the part where I change it to reflect the cells I want?

Please Login or Register  to view this content.

Yes, Cells just specifies a row number and column number, if you prefer you can change it to:

Please Login or Register  to view this content.

Oh, and use sheet names rather than index numbers.

Whatever you're most comfortable with, really...

Please Login or Register  to view this content.

make this simple...this is the file I'm trying to augment...I already added your code to it, but it doesn't seem to want to progress the number....what am I doing wrong?

You have to save the template with a blank in cell I6, because when you create a new workbook from this template it initially checks that this cell is blank to see if it should populate that field (if it didn't do this then it would increment your incident number every time you opened a saved workbook).

Save the template with I6 blank, then create a new workbook using that template and the incremented incident number should be populated.

Can you post the code you're using to do the save?

As for the other issue - not really. There isn't an AfterSave event, only a BeforeSave. What we could do is test when the workbook is closed whether it has been saved or not.

Actually, typing that has made me realise there's going to be another problem with this.

Where do you want to start...

Looks like your save as routine should work fine, although you may want to add a file extension in there.

Please Login or Register  to view this content.

As for the other issue - get rid of the BeforeSave code and replace it with this BeforeClose code ...

Please Login or Register  to view this content.

That should do this job.

the save bit is a Macro correct(first part of your last post)? something I would have to run, it won't just do it when I click save?

ok, I changed the code to before close as you said....I also added the save macro and assigned it to an object, but everytime I click the object("Click to Save" button) it resets to 001 and tries to save it as yy-mm-001.xls and overwrite the existing one. I uploaded what I have again so that you can see it directly

The macro behind your click to save button doesn't seem to be the one in the workbook itself. Can you try reassigning the object to that macro.

Other than that everything seems to be working OK for me.

how's that? I thought I had it right...

That version seems right - it works fine for me now.