Create a function for the next number to be issued

[Guest]

I'm creating a Purchase Order, and I want to have the number increased to the
next number when I open the templet. Any ideas. For example, if the PO is
16500, then the next time I open the file, I want it to be 16501. Can this
function be created?

 

[Pete_UK]

This link explains how to do it:

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

Hope this helps.

Pete

 

[Peo Sjoblom]

You would need VBA


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




on how to get started with macros


http://www.mvps.org/dmcritchie/excel/getstarted.htm


--


Regards,


Peo Sjoblom

 

[Guest]

Peo,

I'm getting a "script out of range" error on line 7 (With ThisWorkbook..

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long
With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

 

[Peo Sjoblom]

Are you using this in a sheet named "Invoice"?


--


Regards,


Peo Sjoblom

 

[Guest]

Yes...I renamed the file "Invoice.xls".

Thanks,

Peo,

I'm getting a "script out of range" error on line 7 (With ThisWorkbook..

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long
With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

 

[Pete_UK]

The sheet that you are using it on has to be called Invoice for the
code to work - not the filename!

Pete