"Autonumber" for forms in Word or Excel?

[penguin1]

I have a form designed in Word in which a manager has to input a case number each time he/she opens the form and fills it out. I would like to have this field filled in automatically either by pulling from a list of numbers in Excel or using an "autonumber" that generates a number each time the form is opened. Any ideas on how to do this? Is there an "autonumber" feature in Word or Excel?

X-) Thanks in advance for your help!

 

[jgoodman00]

I have had a quick & it looks as if you would have to create a macro in order to do this. It is easy enough to fill in the fields with a new number, but word does not appear to reference the table fields, so it becomes a little 'hit & miss' as to where any given value would go.

I think you could achieve this in excel though, because you can reference individual cells... James Goodman
j.goodman00@btinternet.com

 

[penguin1]

James, do you have a procedure on how to do this? I know how to create a macro, but how would I reference a different cell each time the form opened in excel? Is there a "NextCell" function?

 

[SimonDavis]

Try using 'offset'. Off the top of my head I can't remember the syntax, but you can use that to move in any direction from the cell you are in.

I think 'offset (0,1)' will either move it 0 to the right, and 1 down, or it may be the other way round.

If you have a problem, post back and I'll look it up from my dusty archives . . .

 

[ilses]

Penguin,

Presuming you want the case numbers entered in the same column without empty rows in between, it is pretty easy to find the first blank cell in a column.

If, for example, the case numbers are to go into column A, the following will find the first blank cell:

Range("A1&quot.End(xlDown).Offset(1,0).Select

IS

 

[pi]

Penguin

Further to what Ilses had to say try this to insert numbers ascending into column "A".

Range("a1&quot.End(xlDown).Offset(0, 0).Select 'last cell in the column which has a value
c = ActiveCell.Value
Range("a1&quot.End(xlDown).Offset(1, 0).Select ' find the next empty cell in the column
ActiveCell.Value = c + 1 'add one(1) to the value in the cell.

I hope this is what you are looking for

 

[penguin1]

Pi/Ilses, I think we are on the right track here, but I'm not sure where to put the code you gave me. Does it go into a macro? Do I put it into the VB editor within excel somewhere? I apologize for my programming ignorance.

Again, what I am trying to do is create a form within excel that when it opens up a new "case number" will be assigned to the form different from the last. Just clarification, if necessary.

Thanks for your help!

Penguin1

 

[ilses]

Hi Penguin,

I read your initial post to mean that you wanted to have a list of numbers in say column A, adding a new number to the list everytime and have the manager type in data in the rest of the row. Looks like I was wrong. :~/

Suppose you need a template. The manager clicks File, New and selects the template to create a new file, which in a specific cell has a number that is one up from the previous number used for the previous file that was based on the same template. Like the sample Excel template for invoices?

If this is what you are looking for, you can do two things. Take the most appropriate Excel sample template and modify it to suit your needs. Or, create a template with autonumbering from scratch. In the latter case you will need an "auxiliary file" to store the current number (or a registry entry if working on one computer).

The following gives a basic procedure for autonumbering in Excel. Please note: it does not have any error handling to deal with such things as the "auxiliary file" being opened by another user etc.

You add it to the template by pressing Alt+F11. This takes you to the Visual Basic Editor. On the left there is a panel called Project. Below the name of your Excel file, double click This Workbook. In the panel on the right, use the drop down on the left to select Workbook. Paste the code in the right panel. The line preceded by a ' are comments. In the lines following these, you need to make a few changes.

Private Sub Workbook_Open()
Dim rn As Long
Dim fileno As Long

'change "Sheet1" to the name of your sheet
'change "A1" to the cell address of the number cell

If IsEmpty(ThisWorkbook.Sheets("Sheet1&quot.Range("A1&quot.Value) Then
fileno = FreeFile(1)

'change the path and file name, but not the extension, to your path etc.
Open "d:\data\draft\rnnummer.bin" For Binary Access Read As #fileno

Get #fileno, , rn
Close #fileno
rn = rn + 1
'change path and file name, see above
Open "d:\data\draft\rnnummer.bin" For Binary Access Write As #fileno
Put #fileno, , rn
Close #fileno
'change the cell reference as appropriate
Range("A1&quot.Value = rn
End If

End Sub

Give it a go if you like, and see if it's closer to what you're trying to achieve. BTW, something similar is also possible in Word.

IS

 

[deevaetodin]

Could you explain the rnnummer.bin file.

This file was created?

Does this file contain a list of numbers, what application do you create the file in to save it as a BIN file. Can you use any other file types for autonumber generation?

Thanks.

 

[ilses]

bin is for binary file (file format). If it doesn't exist already, the file is created automatically. (The directory is not created automatically.) The bin file only serves as a store for a counter, which is increased everytime the procedure is called. So, no it does not contain a list at all.

It is certainly possible to use other types of files. If you need autonumbering for Excel, for instance, you could also create an Excel xls file to store the current number in say cell A1. You would then write a procedure that opens this file, adds 1 to a the current number in cell A1, assigns this new number to a variable, saves and closes the file, and dumps the value of the variable in the appropriate cell in your new Excel file.

BTW, if you store the procedure in a Workbook_Open event, a new number will be assigned again every time the workbook is opened. It's better to have the Workbook_Open procedure check whether there is already a number in the designated cell, and if not call a Sub with the autonumbering procedure.

For autonumbering in Word an "ini" file could be used in combination with a docvariable field. The ini file stores the current number, to which 1 is added by the autonumbering procedure. If it doesn't exist already the ini file has to be created by a statement or a call to a sub such as:

Sub CreateCounterFile()
'
System.PrivateProfileString("d:\Data\Draft\QUOTE" & Year(Now) & ".INI", "NUMBERING", "LASTNUMBER&quot = "0"
'
End Sub

Because I want to start numbering from 1 every year, and want to be able to tell which counter file is used for what the first part of the file name is QUOTE for the file storing the current quotation number, the year is added to it Year(NOW), plus the extension ".INI".

An AutoNew macro for this:

Sub AutoNew()
'
Dim strNewQuoteNo As String
Dim strLastQuoteNo As String
'
'
With Application.FileSearch
.NewSearch
.LookIn = "d:\data\draft"
.SearchSubFolders = False
.FileName = "QUOTE" & Format(Date, "yyyy&quot & ".INI"
If .Execute() = 0 Then
CreateCounterFile
End If
End With
'
strLastQuoteNo = System.PrivateProfileString("d:\Data\Draft\QUOTE" & Format(Date, "yyyy&quot & ".INI", "NUMBERING", "LASTNUMBER&quot
strNewQuoteNo = strLastQuoteNo + 1
ActiveDocument.Variables("QuoteNo&quot.Value = strNewQuoteNo
strLastQuoteNo = strNewQuoteNo
System.PrivateProfileString("d:\Data\Draft\QUOTE" & Format(Date, "yyyy&quot & ".INI", "NUMBERING", "LASTNUMBER&quot = strNewQuoteNo
ActiveDocument.Fields.Update
'
'
End Sub

The template in which this is stored has a docvariable field called "QuoteNo".

Again, this is a basic example - it certainly isn't userproof.

IS