Numbered forms (xl97)

Topic

New Reply

I have a form which must be numbered sequentially, incrementing by one everytime the master form is opened. Once a fomr has been filed, the number is to remain constant. How do I have it create the number automatically in the first palce and have it stable once the form has been completed and stored.

Reply | Quote

Replies

Intriguing question – but rather depends on what you think of as a form and filing.
For the sake of a reply I assume the form equates to a master workbook used as a template.
I also assume that once you ‘save’ the workbook this equates to ‘filed’. After such a save
you want the next workbook created from the workbooke to be based on the template +1 number?

Should be easy with a BeforeSave Routine
The Counter could be stored in a protected cell, or in a Document Property of the original template.

I’ll assume it is in a cell with name “MyFormCounter”

The following event Macro should do the trick

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Selection.Locked = False
Range(“MyFormCounter”).Value = Range(“MyFormCounter”).Value + 1
Selection.Locked = True
End Sub

Reply | Quote

How does that update the template number? Your code will also update the number EVERY time the workbook is saved.

Reply | Quote

Legare
True – the routine I proposed would increment every save – I wasn’t putting too detailed a job into it until I found out more about the problem.
It would be easy enough to only increment on a Saveas (or force one). I also considered that one could use the file NAME as the form counter and increment it via a Saveas as well

I made the assumption that MyFormCounter was the name of the Form number – VBA code didn’t have to do anything with it.

I liked all your questions – the answers will help work through several of the assumptions I made explicitly and implicitly

Reply | Quote

Thanks. I tried info. Works well but does not perform as I need. Need master form/template. When it is save the number is incremented, however the new workbook created from the template every time it is saved increments as well. I have set up electronic forms for orders. they are e-mailed and saved by various depatments. Using your programing, every time the new work book is saved the number changes.
Can you help eliminate this problems?

Reply | Quote

Before answering, I need the answers to a few questions.

1- Is Andrew’s description of what you want basically corrent? You have a template that you are using to create other workbooks from and you only want to update the sequence number when the workbook created from the template is saved. If the template is used to open a new workbook, and that workbook is closed without saving you do not want to update the number.

2- Is this template shared on a network where multiple users are creating new workbooks from the template.

3- If the workbook is printed before it is saved, what do you want printed as the sequence number. If the template is shared, and you don’t update the number until it is saved, the number could change by the time it is saved.

4- Would you want to force the save immediately after creating a new workbook? If so, where and under what name?

Reply | Quote

1- Template is used to create other workbooks which are distributed to other departments for processing and filing.

2- It is not shared on a network. The new workbook is saved as a file. Yes, I did experience a problem as every time the template or new workbook was saved, the number incremented.

3- The new work book is printed only once for the job file, otherwise everyone in the process loop uses the new workbook file which is e-mailed to them.

4- I tinkered with the VB to force a save of the master/template only so it would be ready for the order entry (new work book).

I do need something to turn the increment off in the new workbook automatically. I do not have any experience with VB but do know how to make minor mods after seeing the lines created when recording a macro.

Reply | Quote

I’m struggling to understand the problem.

So far I think that :
You have a single template.
You use this template to create a Workbook for departments.
This single created workbook represents a single form which is mailed and saved multiple times, but only printed once?

I cannot quite understand if the ‘form’ is in the workbook and always the ‘same’ form or not.

Frankly if there is a single form in a specific workbook the easiest way to achieve uniqueness is to simply name the workbook with the formname as in

Form-12345.xls – where 12345 is the form number

Within the sheet a formula such as

=MID(CELL(“filename”),FIND(“.xls]”,CELL(“filename”),1)-1,5)

would then ‘extract’ the 5 characters just prior to the end of the filename (for numbers of other lengths change the 5 to something else.

However, once again, I’m working on a lot of assumptions due to the fact that I don’t yet understand the problem so will not attempt a detailed robust ‘solution’.
If the above is not close, could you clarify what you think of as a form and describe its “life”. e.g. describe what it is, how it is created , how it is mailed and changed, and when the number is ‘set’.

Thanx in advance

Reply | Quote

I believe that the code below will do what you want. It keeps the sequence number in a file named Counter.txt which is in the same directory as the template. It also puts the sequence number into cell A1 on Sheet1. This code must be placed in the Workbook BeforeSave event routine.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strPath As String
Dim lSeq As Long
Dim iFileNo As Integer
    If Worksheets("Sheet1").Range("A1").Value  "" Then Exit Sub
    lSeq = 0
    iFileNo = FreeFile()
    On Error Resume Next
    Open ThisWorkbook.Path & "Counter.txt" For Input As #iFileNo
    Input #iFileNo, lSeq
    Close #iFileNo
    On Error GoTo 0
    lSeq = lSeq + 1
    Open ThisWorkbook.Path & "Counter.txt" For Output As #iFileNo
    Write #iFileNo, lSeq
    Close #iFileNo
    Worksheets("Sheet1").Range("A1").Value = lSeq
End Sub

Reply | Quote