how to auto assign po numbers in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a po form in Excel and want to be able to auto assign the po number,
so that when this form is filled out and faxed/emailed in that number will
not change.

I know very little about macros.
 
Try this VB code

Private Sub Workbook_Open()
Dim FName As String
Dim FNo As String
Dim x As Long
FName = ThisWorkbook.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 ***
Range("K1").Value = x
Close #FNo
FNo = FreeFile
Open FName For Output As #FNo
Write #1, x
Close #FNo
End Sub

It should go in the ThisWorkbook module which can be accesed by right
clicking on the icon next to File in the main toolbar and selecting View Code.

Hope this helps. This code is thanks to the wonderful folks at the "Mr.
Excel" message board.

Larry
 
Thank you!! I will try this out.

Larry E said:
Try this VB code

Private Sub Workbook_Open()
Dim FName As String
Dim FNo As String
Dim x As Long
FName = ThisWorkbook.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 ***
Range("K1").Value = x
Close #FNo
FNo = FreeFile
Open FName For Output As #FNo
Write #1, x
Close #FNo
End Sub

It should go in the ThisWorkbook module which can be accesed by right
clicking on the icon next to File in the main toolbar and selecting View Code.

Hope this helps. This code is thanks to the wonderful folks at the "Mr.
Excel" message board.

Larry
Click to expand...
 
Back
Top