Sequential Invoice Numbering

[GVERB1219]

Hi all,

Back for my second question here.

I have a receipt template I made in Excel 2003....(yes it's old, but so am I and it's all I have)....

I have an invoice number that I want to have change up by one everytime I open the Receipt Template
The cell for the invoice number is F2
I can get the numbers to change ONLY if every time I'm done I save it as another TEMPLATE

I open the template.......it shows number 1498 in cell F2
I fill in all the customers info to the other cells
I normally save the file as a workbook with customers name in a "Completed Charters" folder
Then close the template
Then when I open the TEMPLATE again, I have a fresh blank page to input the next customers info in.
The ONLY way I can get the numbers to go up by 1 is to ONLY "save as" the file as a TEMPLATE again, then close it, then it tells me it will be saved as 1498, next file will be 1499.
I know all I have to do is open the Template and "overwrite" the old customers info with the new info each time, but
that kinda defeats the purpose of having a template to get a fresh blank page open every time.
I'd like to be able to just save the file normally, then close the template and have a new blank sheet open with the next number in sequence.
Am I asking too much.........

Here's what I have in the

ThisWorkbook for code.......

Option Compare Text
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = True Then Exit Sub
If Not Me.FileFormat = xlTemplate Then Exit Sub
UserForm1.Show
End Sub


Private Sub Workbook_Open()
If Not Me.FileFormat Then Exit Sub
With Sheet2
.Visible = xlSheetVeryHidden
.Range("F2") = .Range("F2")
Sheet2.Range("F2") = .Range("F2") + 1
Me.Save
End With
End Sub

[/URL][/IMG]

 

[GVERB1219]

Sorry.......put the wrong pic up and I can't seem to edit the original post............

[/URL][/IMG]

 

[Teeroy]

Hi GVERB1219,

Your Template adds 1 to cell F2 every time it is opened but if you don't save the Template then the value in cell F2 has not changed so your starting point for the increment is fixed. Try pointing the invoice number in the Template to another file which can hold the current invoice number. You could use another workbook which is automatically opened when the Template is opened, or even a text file.

 

[GVERB1219]

Hi GVERB1219,

Your Template adds 1 to cell F2 every time it is opened but if you don't save the Template then the value in cell F2 has not changed so your starting point for the increment is fixed. Try pointing the invoice number in the Template to another file which can hold the current invoice number. You could use another workbook which is automatically opened when the Template is opened, or even a text file.

I guess I don't understand completely how it all works.
I know when I have the same receipt template without the sequential numbering it works as a template should. I open it and it's a fresh template. I fill in all the info and save it as a workbook file in a "customer receipts" folder. .......then simply close the template without further saving. When I open it again it gives me a clean receipt template. I just don't get why, by having the sequential numbering in place, I have to go through the save as template process again and rename it......Receipts, Receipts1, Receipts11, Receipts111, etc.....I almost think at this point it would be easier to simply remove the sequential numbering and put the numbers in myself..................
I don't see anyway to attach a file in this forum or I'd pass it along for you guys to mess with...........