Problems with invoice..VBA

[TheHealer]

I have an invoice template in which I have added a code that increases
the invoice number by 1 every time it is opened.. however the invoice
has to be overwritten for this to work (or the template) I am new to
all this and am struggling to find out what the heck I am doing here
lol.

I have also inserted a code to empty the contents of the previously
filled in information on the invoice so that it is ready to be filled
out again.. HOWEVER.. this means that even tho I use Save as and save
the invoice as something else when I open that copy .. everything is
reset and the invoice number is incremented just like in the template.

How on earth do I get a working invoice that I can save for future
reference and yet still have the option of opening a blank invoice with
a unique invoice number in it..


This is the only code I have written.. as follows ..

Private Sub Workbook_Open()
Range("l4").Value = Range("l4") + 1
Range("d18:d34,e12:e15,e18:e34,e38,g14:g15,d18:d34,l12:l15,k18:k34").ClearContents



End Sub

Hoping someone can explain in simple terms how to fix my dilemma

 

[TheHealer]

ummmmm did I post this in the wrong forum or does no one know the
answer?

 

[Harald Staff]

Hi

Ok forum. Problem is, you are saving the current "last number" in the last
current file. So you have to use that copy to proceed. What I'd do is save
the number outside somewhere, and when a new empty template is opened;
-prompt for "save as something" else immediately;
-if not cancelled, find next free number, number it and remember it.

Heres' code to read/save increasing numbers. It's in a very small textfile.
Change the path to a network drive to make it work for multiple users:

Sub NewInvoiceNumber()
Dim ThisInvoice As Long
Dim ReadText As String
Dim StoreFile As String

StoreFile = "C:\Temp\Number.num" 'Change
'read previous number:
If Dir(StoreFile) = "" Then 'not found
ThisInvoice = 1
Else
Open StoreFile For _
Input Access Read As #1
While Not EOF(1)
Line Input #1, ReadText
ThisInvoice = Val(ReadText)
Wend
Close #1
End If
ThisInvoice = ThisInvoice + 1
MsgBox "Invoice # " & ThisInvoice
'replace previous with "paste into sheet code"
'Store this number:
Open StoreFile For _
Output Access Write As #1
Print #1, ThisInvoice
Close #1
End Sub

 

[TheHealer]

Thanks HArald I will try that.. ummm.. just one more question tho.. am
right in putting the code in the Workbook area? I put the last one i
Open.. but I really am new to all this so i dont know if thats right.

I will try the code tomorrow.. and let you know if I pull it off..

thanks very much

Eliz