Solved: Stop the Madness

[rbarr]

I copied this macro from another source and modified it to suit my needs. The problem is that I open the new Work Order/Invoice (I get a new number) and add my data, then change the file name & save it. If I were to open the newly renamed workbook again I get a new invoice number.

Q: Can the macro be changed to only generate one number then disable once the file name has been changed?

I'm not very experienced with this stuff so be gentle!!!

Thanks

Below is the beast.....



[vba]
Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Public Function NextSeqNumber(Optional sFileName As String, _
Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "\\Mystic\business\Frame_Shop\Invoice Numbers"
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(9).Range("e5").Value = NextSeqNumber
End Sub
[/vba]

[rbrhodes]

Hi,

Insert this:

[VBA]
nFileNumber = FreeFile

'*****

'Get workook name
sFileName = ThisWorkbook.Name

'Check if new workbook default name = Book#.xls
If Left(sFileName, 4) <> "Book" Then Exit Function

'****

If sFileName = "" Then sFileName = sDEFAULT_FNAME[/VBA]

[rbarr]

Ok..Stupid question time..where do I insert this???

Thank you

[rbarr]

Also, does this need to be ammended in any way to work wit my template, ie, template name?

[Bob Phillips]

[vba]

Public Sub Workbook_Open()
If ThisWorkbook.Path = "" Then
ThisWorkbook.Sheets(9).Range("e5").Value = NextSeqNumber
End If
End Sub
[/vba]

[rbarr]

Solved..Thank you