Does anyone know how to set up a 'master' form that is pre-numbered, or auto advances with every use. Example: As the form is used, it is printed as form number 1000, next use 1001, 1002 and so on. Need it to automatically number it to avoid human error.

Currently using the form on one sheet, with a log on another. Have tried using macro's, functions, etc. Can't seem to find the right combo. All templates I have seen do not have auto numbering, so I have a feeling it is beyond excel's capabilities, but just in case I missed something (new to excel, former Lotus user, new job).

Using Excel 2002.

Thanks for your thoughts and/or ideas.

Strider,

Depends what you mean by every time it is used, but let's assume you mean
every time it is opened. A simple workbook open event proc will handle it.
Again, I assume that the incremental number is on sheet 1 range A1, adjust
to suit

Private Sub Workbook_Open()

With Worksheets(1).Range("A1")
If Len(.Value) = 0 Or Not IsNumeric(.Value) Then
.Value = 1000
Else
.Value = .Value + 1
End If
End With

End Sub


This goes into the ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Strider" wrote in message
...
Does anyone know how to set up a 'master' form that is pre-numbered, or
auto advances with every use. Example: As the form is used, it is printed as
form number 1000, next use 1001, 1002 and so on. Need it to automatically
number it to avoid human error.

Currently using the form on one sheet, with a log on another. Have tried
using macro's, functions, etc. Can't seem to find the right combo. All
templates I have seen do not have auto numbering, so I have a feeling it is
beyond excel's capabilities, but just in case I missed something (new to
excel, former Lotus user, new job).

Using Excel 2002.

Thanks for your thoughts and/or ideas.

Well, sort of. Its a master spreadsheet, saved to a new file and then filled in. Trying to place a pre-set number on the Master that increases by one every time it is used, or prior to saving as another file. Needs to be numerical for tracking purposes. The working excel file gets deleted after a number of weeks and a hard copy of the used form, hopefully with the pre-set number on it, is saved.

I will try your suggestion, much farther then I got. Only concern would be increasing the number by one everytime it is opened. My miss a few along the way if opened and not used.

Thanks Bob

Strider.
I will try your suggestion, much farther then I got. Only concern would be
increasing the number by one everytime it is opened. My miss a few along the
way if opened and not used.


You could accommodate this as if the workbook is not changed, when closed it
will not be saved, so that increment will not be saved. Problem is, that by
doing the increment in workbook open, the workbook is changed. However, this
can be handled by setting the workbook Saved property to true immediately
after the increment, Like this

Private Sub Workbook_Open()

With Worksheets(1).Range("A1")
If Len(.Value) = 0 Or Not IsNumeric(.Value) Then
.Value = 1000
Else
.Value = .Value + 1
End If
End With

ThisWorkbook.Saved = True

End Sub

I find that saving the master file down each time
the "autonumber" increments is a poor idea. I tend to use
a number of methods.
The hardest is to use a SQL database where key data is
located and where each time I generate an invoice,
details get placed in a table - the table iD is used as
my autonumber. This is a great way to track stuff too.
Another method that I have used is simply to place a text
file in a "well known" location - say a folder on a
network, so that it can be quickly read and written to by
any excel workbook. This is great for where there are a
number of people that may generate invoices from a given
template. The risk of a collision is quite minute since
writing a number to a text file or reading it, is
blindingly fast!



Option Explicit

Const csINVOICEFILE As String _
= "C:\Temp\InvoiceNumber.txt"

Private Sub cmdNewNumber_Click()
' reads current number
'increments it
' saves it
Dim ff As Long
Dim Invoice As Long

ff = FreeFile
Open csINVOICEFILE For Input As ff
Input #ff, Invoice
Close ff

Invoice = Invoice + 1
lblInvoiceNumber.Caption = Invoice

ff = FreeFile
Open csINVOICEFILE For Output As ff
Print #ff, Invoice
Close

End Sub

Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Does anyone know how to set up a 'master' form that is
pre-numbered, or auto advances with every use. Example:
As the form is used, it is printed as form number 1000,
next use 1001, 1002 and so on. Need it to automatically
number it to avoid human error.

Currently using the form on one sheet, with a log on
another. Have tried using macro's, functions, etc. Can't
seem to find the right combo. All templates I have seen
do not have auto numbering, so I have a feeling it is
beyond excel's capabilities, but just in case I missed
something (new to excel, former Lotus user, new job).

Using Excel 2002.

Thanks for your thoughts and/or ideas.
.

Thanks Bob and Patrick, I'll try each and let you know how things turned out.