hiya, i'm a newbie around here, and i have a problem.

i'm making an invoice/stock control system.

my question is how can make the invoice number on the template update
its self every time i open excel?

so for example, if i were to have invoice no.1. the next time i open
excel i want that to automatically change to "2".

thanks.
Z


---
Message posted from http://www.ExcelForum.com/

Basically you need an independent place to store data that
your macro can draw from each time it starts. Here's one
way to do what you are looking for. See my further
comments at the end.

Private Sub Workbook_Open()
On Error GoTo 88
Workbooks.Open Filename:= _
"C:\Documents and Settings\My Documents\UserPrefs.xls"
InvoiceNumber = Range("A1").Value
InvoiceNumber = InvoiceNumber + 1
Range("A1").Value = InvoiceNumber
ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
If NeverTrue = 3.14 Then
88 Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\My
Documents\UserPrefs.xls"
InvoiceNumber = 1
Range("A1").Value = 1
ActiveWorkbook.Save
End If
End Sub

1) You will have to add code to set the invoice number
(wherever it is located on your sheet) to the
variable "InvoiceNumer"
2) You should put this code in the "ThisWorkbook" code
area which is an item in the Project Explorer in the
VisualBasic Editor under Microsoft Excel Objects in your
VBAProject item. This will allow the code to execute
immediately after the file is opened.
3) You could save the "UserPrefs" file anywhere on the
user's drive just so long as it will not be deleted or
moved. If it is deleted or lost then you lose the current
invoice number, but you can make a new UserPrefs file and
manually enter the number from the last invoice in cell
A1, then resave the file to the proper location.

Have fun!

-IA

-----Original Message-----
hiya, i'm a newbie around here, and i have a problem.

i'm making an invoice/stock control system.

my question is how can make the invoice number on the
template update
its self every time i open excel?

so for example, if i were to have invoice no.1. the next
time i open
excel i want that to automatically change to "2".

thanks.
Z


---
Message posted from http://www.ExcelForum.com/

.

thankyou very much, i'll have to try that one out.

i appreciate your help.

Z'


---
Message posted from http://www.ExcelForum.com/

that coding doesnt seem to work for me, um, i copied it into my
worksheet...i'm a bit lost as to where to go from now.


---
Message posted from http://www.ExcelForum.com/

i managed to sort a coding out. as follows:

Private Sub Workbook_Open()

Dim LOldVal As Integer
Dim LNewVal As String

'Retrieve current number
LOldVal = Sheets("Sheet1").Range("A2").Value
LNewVal = Format(LOldVal + 1, "0000")

'Update to new number
Sheets("Sheet1").Range("A2").Value = "'" & LNewVal

End Sub

thanks for your help in any case, i think this coding is a bit mor
straight forward for me.

Z

--
Message posted from http://www.ExcelForum.com