Incrementing no.s creating new but not changing no. opening saved
 

I have used the following steps to automatically populate the PO number field
in a Purchase Order file, based on a template, with an incremental number .
It works perfectly!!

1. Create a folder called Base Data and within it create a file called
PONumber.xls

2. Type the next new PO number in cell A1 of Sheet1.

3. Press Alt+F11 and double click on the "This Workbook" node for
PONumber.xls. (Make sure it says "PONumber -[ThisWookbook (Code)]" in the
title bar.)

4. Copy and paste in the following code

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Dim number
number = Range("A1").Value
number = number + 1
Range("A1").Value = number
Range("A1").Select
End Sub

5. Save and close this file.

6. Open your PO template file and in the cell where you want the number to
appear type this formula:

='C:\Base Data\[PONumber.xls]Sheet1'!$A$1

in the cell where you want the number to appear.

7. Press Alt +F11 and double click on the "ThisWorkbook" node for your
template (check the title bar) then copy and paste in the following code:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Base Data\PONumber.xls"
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

8. Save the template

HOWEVER, whenever I later open the saved populated Purchase Order the
numbers is changed. For examplte, if when the file was populated the PO
number was "PO0115" and a number of new POs have been saved since the
original file was created, when I open file "PO0115" it's PO number on screen
changes to the next new number held in my PONumber file e.g. "PO0119". How
do I stop this?

Incrementing no.s creating new but not changing no. opening saved
 

As long as you allow links to recalculate, then this formula will always grab
the current po from the ponumber.xls workbook.

='C:\Base Data\[PONumber.xls]Sheet1'!$A$1

You may want to try this change in the template file.

First, put this in the cell that holds the PO Number:
PONUMBER
(It'll be an indicator to see if you should retrieve a new PO.)

Then in the ThisWorkbook module of the template:

Private Sub Workbook_Open()
'what cell on what worksheet holds the PO number?
with me.worksheets("Sheet9999").range("A1")
if lcase(.value) = lcase("PONUMBER") then
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Base Data\PONumber.xls"
'retrieve the current value from the ponumber.xls file
.value = activeworkbook.worksheets("sheet1").range("a1").va lue
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
end if
end with
End Sub

You may want to review JE McGimpsey's notes:
http://mcgimpsey.com/excel/udfs/sequentialnums.html

ml123 wrote:

I have used the following steps to automatically populate the PO number field
in a Purchase Order file, based on a template, with an incremental number .
It works perfectly!!

1. Create a folder called Base Data and within it create a file called
PONumber.xls

2. Type the next new PO number in cell A1 of Sheet1.

3. Press Alt+F11 and double click on the "This Workbook" node for
PONumber.xls. (Make sure it says "PONumber -[ThisWookbook (Code)]" in the
title bar.)

4. Copy and paste in the following code

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Dim number
number = Range("A1").Value
number = number + 1
Range("A1").Value = number
Range("A1").Select
End Sub

5. Save and close this file.

6. Open your PO template file and in the cell where you want the number to
appear type this formula:

='C:\Base Data\[PONumber.xls]Sheet1'!$A$1

in the cell where you want the number to appear.

7. Press Alt +F11 and double click on the "ThisWorkbook" node for your
template (check the title bar) then copy and paste in the following code:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Base Data\PONumber.xls"
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

8. Save the template

HOWEVER, whenever I later open the saved populated Purchase Order the
numbers is changed. For examplte, if when the file was populated the PO
number was "PO0115" and a number of new POs have been saved since the
original file was created, when I open file "PO0115" it's PO number on screen
changes to the next new number held in my PONumber file e.g. "PO0119". How
do I stop this?

--

Dave Peterson

Incrementing no.s creating new but not changing no. opening sa
 

Many thanks. I managed to figure out that the problem was with the
Workbook_Open macro. I added a button calling a macro getting the next
sequential number from the PONumber worksheet and removed the Workbook_Open
macro. All now seems to be well.

"Dave Peterson" wrote:

As long as you allow links to recalculate, then this formula will always grab
the current po from the ponumber.xls workbook.

='C:\Base Data\[PONumber.xls]Sheet1'!$A$1

You may want to try this change in the template file.

First, put this in the cell that holds the PO Number:
PONUMBER
(It'll be an indicator to see if you should retrieve a new PO.)

Then in the ThisWorkbook module of the template:

Private Sub Workbook_Open()
'what cell on what worksheet holds the PO number?
with me.worksheets("Sheet9999").range("A1")
if lcase(.value) = lcase("PONUMBER") then
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Base Data\PONumber.xls"
'retrieve the current value from the ponumber.xls file
.value = activeworkbook.worksheets("sheet1").range("a1").va lue
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
end if
end with
End Sub

You may want to review JE McGimpsey's notes:
http://mcgimpsey.com/excel/udfs/sequentialnums.html

ml123 wrote:

I have used the following steps to automatically populate the PO number field
in a Purchase Order file, based on a template, with an incremental number .
It works perfectly!!

1. Create a folder called Base Data and within it create a file called
PONumber.xls

2. Type the next new PO number in cell A1 of Sheet1.

3. Press Alt+F11 and double click on the "This Workbook" node for
PONumber.xls. (Make sure it says "PONumber -[ThisWookbook (Code)]" in the
title bar.)

4. Copy and paste in the following code

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Dim number
number = Range("A1").Value
number = number + 1
Range("A1").Value = number
Range("A1").Select
End Sub

5. Save and close this file.

6. Open your PO template file and in the cell where you want the number to
appear type this formula:

='C:\Base Data\[PONumber.xls]Sheet1'!$A$1

in the cell where you want the number to appear.

7. Press Alt +F11 and double click on the "ThisWorkbook" node for your
template (check the title bar) then copy and paste in the following code:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Base Data\PONumber.xls"
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub

8. Save the template

HOWEVER, whenever I later open the saved populated Purchase Order the
numbers is changed. For examplte, if when the file was populated the PO
number was "PO0115" and a number of new POs have been saved since the
original file was created, when I open file "PO0115" it's PO number on screen
changes to the next new number held in my PONumber file e.g. "PO0119". How
do I stop this?

--

Dave Peterson