Have P.O. increment by 1 only on Save As
 

Could someone help me with a problem that a co-worker is having. What they
would like to do is have a P.O.increment to the next number. I thought I
found the answer. After creating a template and pasting inthe following
code. It appears to work. When I open the template the number is assigned
as I want. But when I save an XLS spreadsheet, all looks good. That is
until I open the spreadsheet and the number increases by one in the P.O.
number field. The same thing happens when I open the template. Below is
the code I'm using from mcgimpsey. What I would like to be able to do is
have the number assigned to the next highest number, but remain static if I
open the XLS spreadsheet and remain static in the Template until I do a save
as to an XLS spreadsheet. And not automatically sequence to the previous
number plus one upon opening the template.Any help would be appreciated.I've
googled the posts for this subject and copied the following code from
mcgimpsey. http://www.mcgimpsey.com/excel/udfs/...ums.htmlPublic
Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As
Long = -1) As Long
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator &
sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function
Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber End Sub

Have P.O. increment by 1 only on Save As
 

In the thisworkbook code module add this event code, instead of using your
current open event...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
if SaveAsUI = true then
'increment the PO number...
end if
End Sub

Not there is a SaveAsUI which you can use to catch if it is a save as and
then increment the PO number...
--
HTH...

Jim Thomlinson


"Joel" wrote:

Could someone help me with a problem that a co-worker is having. What they
would like to do is have a P.O.increment to the next number. I thought I
found the answer. After creating a template and pasting inthe following
code. It appears to work. When I open the template the number is assigned
as I want. But when I save an XLS spreadsheet, all looks good. That is
until I open the spreadsheet and the number increases by one in the P.O.
number field. The same thing happens when I open the template. Below is
the code I'm using from mcgimpsey. What I would like to be able to do is
have the number assigned to the next highest number, but remain static if I
open the XLS spreadsheet and remain static in the Template until I do a save
as to an XLS spreadsheet. And not automatically sequence to the previous
number plus one upon opening the template.Any help would be appreciated.I've
googled the posts for this subject and copied the following code from
mcgimpsey. http://www.mcgimpsey.com/excel/udfs/...ums.htmlPublic
Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As
Long = -1) As Long
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator &
sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function
Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber End Sub

Have P.O. increment by 1 only on Save As
 

Thanks for your help. This does exactly what I want. It only changes on
Save As.

Joel

"Jim Thomlinson" wrote in message
...
In the thisworkbook code module add this event code, instead of using your
current open event...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
if SaveAsUI = true then
'increment the PO number...
end if
End Sub

Not there is a SaveAsUI which you can use to catch if it is a save as and
then increment the PO number...
--
HTH...

Jim Thomlinson


"Joel" wrote:

Could someone help me with a problem that a co-worker is having. What
they
would like to do is have a P.O.increment to the next number. I thought I
found the answer. After creating a template and pasting inthe following
code. It appears to work. When I open the template the number is
assigned
as I want. But when I save an XLS spreadsheet, all looks good. That is
until I open the spreadsheet and the number increases by one in the P.O.
number field. The same thing happens when I open the template. Below is
the code I'm using from mcgimpsey. What I would like to be able to do is
have the number assigned to the next highest number, but remain static if
I
open the XLS spreadsheet and remain static in the Template until I do a
save
as to an XLS spreadsheet. And not automatically sequence to the previous
number plus one upon opening the template.Any help would be
appreciated.I've
googled the posts for this subject and copied the following code from
mcgimpsey. http://www.mcgimpsey.com/excel/udfs/...ums.htmlPublic
Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber
As
Long = -1) As Long
Const sDEFAULT_PATH As String = "<your path here"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
Dim nFileNumber As Long

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator &
sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) < "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber
nSeqNumber = nSeqNumber + 1&
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function
Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("B2").Value = NextSeqNumber End Sub