PDA

View Full Version : Solved: Stop the Madness



rbarr
11-29-2006, 07:16 AM
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!!!: pray2:

Thanks

Below is the beast.....




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

rbrhodes
11-29-2006, 08:33 AM
Hi,

Insert this:


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

rbarr
11-29-2006, 08:56 AM
Ok..Stupid question time..where do I insert this???

Thank you

rbarr
11-29-2006, 08:59 AM
Also, does this need to be ammended in any way to work wit my template, ie, template name?

Bob Phillips
11-29-2006, 09:07 AM
Public Sub Workbook_Open()
If ThisWorkbook.Path = "" Then
ThisWorkbook.Sheets(9).Range("e5").Value = NextSeqNumber
End If
End Sub

rbarr
11-29-2006, 09:18 AM
Solved..Thank you