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

  • Thread starter Thread starter ml123
  • Start date Start date
M

ml123

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?
 
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").value
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
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?
Click to expand...
 
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 said:
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").value
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
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?
Click to expand...
Click to expand...
 
Back
Top