sequential numbering macro - how to stop it increasing when document is saved??

I have the following code working in such that it will create a new number with increments of 1 each time the document is opened however the document I have is a contract template. What I need is the template to create a new number when its opened and then once its been saved the new saved document keeps the number given to it i.e the contract number remains constant as its important that it doesn't change.

So I guess I need some way for the code to stop working once it knows its been saved if thats possible? I know its possible in Word as this is originally where the contracts were created however our team are wishing to change them into Excel for the additional features Excel gives us but this is a stumbling block if it doesn't work. The contract number comes from a text document called "defaultseq.txt" and I am using Excel 2003.

Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("ad4").Value = NextSeqNumber
End Sub

Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "C:\Documents and Settings\cmansfie\Desktop\macro"
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

A lot of the above I don't know what it is doing as I am a novice to VBA programming however I have managed to get it working almost as I needed to.

Any help very much appreciated. The problem I have mainly is I don't know if I need extra code or whether there's something I can alter thats already there!

Cheers, Chris

hey try this code

Please Login or Register  to view this content.

NOTE THE DELETELINES 1,33 are refrenences to the 1st line of code to the 33rd .

so here is what will happen : since the code is ran in order : monce you open the book it will run the number generator then it will call the delete part and once the delete part run it just deletes every between those two numbers

change your open work book the this

Please Login or Register  to view this content.

then add this somwhere in the workbook

Please Login or Register  to view this content.

this works for 2010 so let me know

Hi, thanks so much for your help however I'm afraid it didn't work

I got the following error -

run time error '1004':

Programmatic access to visual basic project is not trusted

I added it into the module after the number sequence code and not in the Microsoft excel objects>ThisWorkbook section which I think must be correct?

Cheers, Chris

try this

On the Tools menu, point to Macro, and then click Security.
In the Security dialog box, click the Trusted Sources tab.
Select the Trust access to Visual Basic Project check box.
Click OK.

That kinda worked but then gave me another error

I had an idea but no idea how I would implement it -

Could there be an If argument whereby once the number had been created then the sheet saved with the number.....the next time it was opened the macro checked to see if there was a number assigned already and if there was it therefore didn't run the rest of the macro. This way only completely new contracts would obtain a new number??

Does that make sense??

Cheers, Chris

im not sure bacause i am using the same macro that you have an i ran in the same issue and that how i resolved it. however i am using 2010 so maybe theirs a verbiage issue

what the error code or what not happining

Hi again,

Got it working now, just needed a little 1 line addition to this section, can't remember exactly but it was an "if" which checked to see if the cell was blank before running the rest of the macro, hence if it wasn't blank, a number had already been assigned and there was therefore no reason to increment the number.

Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("ad4").Value = NextSeqNumber
End Sub

Cheers for your help anyways