Sequential invoice numbers

[2512chrisb]

Hi all, this has no doubt been asked many times...

I had an Excel invoice up and running with auto date and invoice numbers. I accidentally saved over it so that the date and invoice number became static.

I have been unable to get it to work again, been trying for ages! since Friday 13th when I messed it up!

I have cut and pasted from:http://www.mcgimpsey.com/excel/udfs/sequentialnums.html where I originally got the code. but can't get it to work now.

I would need the invoice numbers to start 1001, otherwise I presume I'd have to open and close the file loads of times to avoid duplicating previously used invoice numbers from when the thing was working?

Any help would be much appreciated!

Many many thanks in advance.



P.s was getting error 9 subscript out of range, got it opening without error messages, but not counting up, then tried different codes from different places, now I'm totally lost.



Edit: I have now put attached the correct file.
Chris.

[kassieK]

You want to change the invoice number, but the invoice is on sheet Invoice3, yet your code refers to invoice.
Change the code to read invoice3.
In sheet Invoice3, change the number to 1000, then save and close the document. When you open it again, the number should be 1001.

[2512chrisb]

Still not working KassieK, but thanks anyway.

I know that throwing the computer out the window wont help, I'm still tempted though.

[kassieK]

I changed your code as follows, and it works?

Private Sub Workbook_Open()
'unprotect
ThisWorkbook.Sheets("invoice3").Range("E8") = ThisWorkbook.Sheets("invoice3").Range("E8") + 1
'reprotect
End Sub

[kassieK]

Sorry, I also deletes the spaces above your code, maybe that is why mine works?

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

That code will only ever work on one sheet. Unfortunately I think this will be only your first problem with your proposed method of invoicing.

[2512chrisb]

I wish i knew what I was doing!

[2512chrisb]

Really sorry I left the wrong code in there, from when I was trying different ones.

I have attached another file...hopefully with the correct code.

I have got the other one working now, but to save it and make it count up again would mean saving over the master copy and deleting the added text upon reopening?

[kassieK]

2512chrisb,

May I suggest you send me an email, then I will send you my invoice workbook.
What it does, is to save the completed worksheet as the invoice number.
It then reopens the master sheet, increments the number by 1, and saves it again before optionally closing it.
So, you open the master sheet, fill in the details, either through auto populating details, or typing it in, and click on the special print button.
It prints out 2 copies, saves the file, eg as Inv 1001.xls, reopens the master, increments the number to 1002, saves the master and closes it.
I think that is what you are looking for?
My address is [email protected]. Just replace the x's with hotmail.