I have an excel spreadsheet that is used for purchase orders. I would like
to be able to increase the Purchase Order number automaticly by one each time
the spreadsheet is opened.

eg Order 1002 on 18/01/2006 becomes Order 1003 the next time the sheet is
accessed.

Any suggestions?

Hi Micheele,

See JE McGimpsey's sequential numbers page at:

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html


---
Regards,
Norman


"Michelle" wrote in message
...
I have an excel spreadsheet that is used for purchase orders. I would like
to be able to increase the Purchase Order number automaticly by one each
time
the spreadsheet is opened.

eg Order 1002 on 18/01/2006 becomes Order 1003 the next time the sheet is
accessed.

Any suggestions?

If the link provided by Norman Jones does not help you. Basically, you have
need to add code to your Workbook_open module (located in the ThisWorkbook
portion of the VBA editor) that will run the macro written to update the
purchase order number.

The code for your purchase order number will look at the cell
(Sheet1.Range("A1")) assign a variable to the value of
Sheet1.Range("A1").value. Then increment that value by one. Then do a
Sheet1.Range("A1").value = new value.

Public sub IncrementByOne()
Dim PurOrderNum as long

PurOrderNum = Sheet1.Range("A1").value
PurOrderNum = PurOrderNum + 1
Sheet1.Range("A1").value = PurOrderNum

end sub

This middle code could be shortened to:
Sheet1.Range("A1").value = Sheet1.Range("A1").value + 1

instead of the three lines. I wrote it out so that it might make some
algebraic sense. :)

Under Microsoft Excel Objects of the Project Explorer Window you should be
able to find the ThisWorkbook "worksheet".

If you double click on it you will have code window to enter in the code to
cause the purchase order number to increase on each open of the workbook.

If you paste:

Option Explicit

Private Sub Workbook_Open()
IncrementByOne()
End Sub

and the above IncrementByOne code into this coding area, then you will get
the results that you want. :)


"Michelle" wrote:

I have an excel spreadsheet that is used for purchase orders. I would like
to be able to increase the Purchase Order number automaticly by one each time
the spreadsheet is opened.

eg Order 1002 on 18/01/2006 becomes Order 1003 the next time the sheet is
accessed.

Any suggestions?