Unique document numbering

[KJH64]

Hello all
Its been a while since I have needed to post but this one has me stumped.
I have searched through archives over the past few days and cannot find help that exactly fits this problem.

I have created a template for a form that will be used many times a day. The idea is that each use of the form will generate a unique number (ie serial number) eg Y2001, Y2002 etc.
I have tried the following coding to change the serial number by one each time the form is opened (which is part of the problem);

Private Sub workbook_open()
Sheets("Permit to Work PG 1").Range("C2").Value = Sheets("Permit to Work PG 1").Range("C2").Value + 1
End Sub

However when I go to re-access the file for whatever reason, the serial number changes by 1.
It needs to remain fixed once used for that unique file.

The problem is that creating the form from the template and saving it as an .xls means the template does not save and therefore the serial number in the template does not increment for the next use.

The next part of the problem is once the form filled out it is to be saved with that unique number and the date in the filename to identify it.

Any suggestions will be greatly appreciated, thanks.

Ken

 

[jbeaucaire]

I use this methodology... a workbook_open macro updates the invoice number in the template-based workbook that opens. A workbook_beforeSave macro reopens the original template, updates the invoice number, saves it, then continues with the "save" function of the current workbook.

Both these modules go in your ThisWorkbook module. It uses call AA1 to check if the template has been updated or not. This also keeps future "saves" from incrementing the invoice on an existing workbook or the template again.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Sheets("Permit to Work PG 1").Range("AA1") = "" Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False
        
        Workbooks.Open Filename:= _
            "C:\Excel Templates\!80 kilo.xlt", Editable:=True
        With ActiveWorkbook.Sheets("Permit to Work PG 1")
            .Range("C2").Value = .Range("C2").Value + 1
        End With
        ActiveWorkbook.Close True
    
        Sheets("Permit to Work PG 1").Range("AA1") = "Incremented"
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If

End Sub

Private Sub WorkBook_Open()

    With Sheets("Permit to Work PG 1")
        If .Range("AA1") = "" Then .Range("C2").Value = .Range("C2").Value + 1
    End With
        
End Sub

Edit the code in red to the full path of the template itself.

 

[KJH64]

Thanks for the reply. I have a question, does the code go into the template or to the first document created. I ask this as I have tried putting it into the template and each time I attempt to save the template it crashes.

 

[jbeaucaire]

It goes into the ThisWorkbook module of the Template file itself. Then you turn off MACROS by clicking on the DESIGN MODE button of the Control Toolbar, then you can save the template without triggering the macro, causing a crash.


It's important that you do not open the template directly when you're trying to use it to make documents, you need to generate a new document based on the template. So:

1) double-clicking the file on the hard drive will do that
2) Creating a shortcut to the .xlt or .xltb file then double clicking the shortcut to create a new document will do that
3) Saving the template into your normal template directly so you can select it as an option whenever you do File > New.

Anyway, you'll know you opened the file correctly when your new document does not have an extension, rather it has a number added by Excel. If the template were template.xlt, the new file would be template1 or template2, etc.

Once that has happened properly, when you do your first SAVEAS to give this new document a permanent name, it will secretly open the actual template a second time, increment the invoice number, resave the template, then insert that invoice number permanently into your new document, then proceed with a normal saveas.


My sample file is found here:

Self-Contained AutoNumbering Invoice Template​

The sample invoice template is included, the macro will need to be edited to include the path to the invoice.xlt path on your system.

 

[KJH64]

Hi Jerry
I really appreciate your help with this one. However, I'm not sure if I'm doing something wrong as I keep getting errors. The bolded line is where it is highlighting a "Compile Error, Expected End With" in the 'Permit To Work1' file as i do a save as. This is my coding:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("PERMITtoWORKpg1")
If .Range("AA1") = "" Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Workbooks.Open Filename:= _
"C:\Documents and Settings\kjhuntle\Application Data\Microsoft\Templates\Permit to Work.xlt", Editable:=True
With ActiveWorkbook.Sheets("PERMITtoWORKpg1")
.Range("C2").Value = .Range("C2").Value + 1
End With
ActiveWorkbook.Close True

Sheets("PERMITtoWORKpg1").Range("AA1") = "Incremented"
Application.ScreenUpdating = True
Application.EnableEvents = True
End If

End Sub
Private Sub workbook_open()

With Sheets("PERMITtoWORKpg1")
If .Range("AA1") = "" Then .Range("C2").Value = .Range("C2").Value + 1
End With

End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheets("PERMITtoWORKpg1").Range("AA1") = "" Then
MsgBox "You must save the workbook before printing is allowed."
Cancel = True
End If
End Sub

Thanks again for your assistance (and patience)

Cheers
Ken

 

[KJH64]

I think I just fixed my error . Seems to be working correctly now.

 

[jbeaucaire]

You removed a key feature to the new macro. The SAVEAS macro needs to see what the new value in the template is, remember it, and insert it into the new sheet after the template closes, even if it appears to be the same number. I'm sure in your testing that appeared unnecessary because the numbers were the same, right? Well, I can envision a scenario where it would NOT be the same, which is why I added this.

The TEMPLATE is on the network and can be opened by multiple people at the same time. If three people opened the same template at the same time, all three would have the same "temporary" number in their new workbook. But the first person to do a SAVEAS would get to keep that new number, the next two would need to have their numbers incremented even higher, right?

Even if the template is on your computer only, YOU could open more than one new sheet at a time, right? You would still want this to perform correctly even if you opened 10 sheets before saving one.

You should put back in the NewVal code.

 

[KJH64]

You lost me with the NewVal code bit. I didn't see anything about that in your code you posted above?? What I have now seems to be working ok but as you say if it is to be accessed by multiple persons at once it could fall down.

 

[jbeaucaire]

My apologies. In post #4 I gave you a link to the original macro which includes the "newVal" code, I could've sworn I left that in.... my apologies.

If you want to see the problem I'm talking about, use your existing template to open 3 new workbooks without saving any of them.

Then save all three... you'll have three workbooks with the same invoice number when you're done... oops.

If you use the version from post #4, each time a new workbook is saved for the first time, it goes and GETS the correct number to use from the template again, so you should never get duplicates.

 

[jbeaucaire]

Also, that version has another macro to correctly keep the new workbook from being printed until AFTER it has been saved at least once. You wouldn't need the Workbook_Open macro at all anymore.