Invoice

[Pauline]

Hi, I have built an invoice using Excel 2000, have inserted all the
necessary formulas and protected it, then saved it as a template in Excel.
I have put a label which says Invoice No. and in the next cell tried various
formulas to make the number increase by one every time the invoice is loaded
from the template, but can't make it work, it just displays Invoice No 1
every time. I would like it to say Invoice 1, Invoice 2, Invoice 3 etc each
time. Not sure how to go about it.

Any advice would be gratefully received. Thanks in advance.

Pauline

 

[Paul B]

Pauline, here is a macro by Andrew Poulsom that will do it, put in this
workbook code, will increase the valve in A1 when you open the template,
change A1 to your cell.

Private Sub Workbook_Open()
' By Andrew Poulsom
Dim FName As String
Dim FNo As String
Dim x As Long
FName = Application.Path & Application.PathSeparator & "Number.Txt"
FNo = FreeFile
x = 0
On Error Resume Next
Open FName For Input As #FNo
Input #FNo, x
x = x + 1
' *** Change range reference to suit ***
If Range("A1").Value <> "" Then Exit Sub
Range("A1").Value = x
Close #FNo
FNo = FreeFile
Open FName For Output As #FNo
Write #1, x
Close #FNo
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Pauline]

Thanks for the help.....Pauline

Pauline, here is a macro by Andrew Poulsom that will do it, put in this
workbook code, will increase the valve in A1 when you open the template,
change A1 to your cell.

Private Sub Workbook_Open()
' By Andrew Poulsom
Dim FName As String
Dim FNo As String
Dim x As Long
FName = Application.Path & Application.PathSeparator & "Number.Txt"
FNo = FreeFile
x = 0
On Error Resume Next
Open FName For Input As #FNo
Input #FNo, x
x = x + 1
' *** Change range reference to suit ***
If Range("A1").Value <> "" Then Exit Sub
Range("A1").Value = x
Close #FNo
FNo = FreeFile
Open FName For Output As #FNo
Write #1, x
Close #FNo
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **