How do I auto number cells in an Excel template?

[Natalie]

I created a template for a purchase order in Excel. Everytime someone opens
the template, I would like one cell to be automatically numbered in a
sequential pattern. That way each time the template is used, it will have
it's own unique number that will be used to identify it. Does anyone know if
this is possible for Excel 2000?

Thanks!

[MWE]

Hi Natalie,

I am a little confused about the sequential numbering of a particular cell each time the workbook is opened. That is pretty easy to do, but the method depends on what will happen to the spreadsheet after the user is "done":

Will each use of the template generate a new spreadsheet that is subsequently saved with a unique name?

or will each user use a new sheet within the same spreadsheet?

or will each user use the template, print it out and then exit the template without saving it?

or will each user use the template, save and then exit the template leaving their junk behind?

or something else?

[Jason Morin]

You could add a 1 to Sheet1!A1 each time the workbook is
opened. Press ALT+F11, double-click the "ThisWorkbook"
module under View>Project Explorer and paste in:

Sub Workbook_Open()
With Sheets("Sheet1").[A1]
.Value = .Value + 1
End With
ActiveWorkbook.Save
End Sub

--
HTH
Jason
Atlanta, GA


>-----Original Message-----
>I created a template for a purchase order in Excel.
Everytime someone opens
>the template, I would like one cell to be automatically
numbered in a
>sequential pattern. That way each time the template is
used, it will have
>it's own unique number that will be used to identify
it. Does anyone know if
>this is possible for Excel 2000?
>
>Thanks!
>.
>

[Natalie]

Hello,

Your suggestion didn't work for me. Can you help me figure out if I did
something wrong? I did all the steps as you suggested, closed MS visual
basic and returned to excel. When I reopened the file, it said that there
was an error and would I like to debug. When I said yes, it took me to
visual basic and highlighted the line:
With sheets("Sheet1").[A1]

Thinking that the problem is that I want to auto number cell A2, not A1, I
changed the cell number and saved. This kept me from having messages saying
I need to debug, but didn't make the spreadsheet automatically number itself
each time it is opened.

Thanks for any help you can offer!

Natalie

"Jason Morin" wrote:

> You could add a 1 to Sheet1!A1 each time the workbook is
> opened. Press ALT+F11, double-click the "ThisWorkbook"
> module under View>Project Explorer and paste in:
>
> Sub Workbook_Open()
> With Sheets("Sheet1").[A1]
> .Value = .Value + 1
> End With
> ActiveWorkbook.Save
> End Sub
>
> --
> HTH
> Jason
> Atlanta, GA
>
>
> >-----Original Message-----
> >I created a template for a purchase order in Excel.
> Everytime someone opens
> >the template, I would like one cell to be automatically
> numbered in a
> >sequential pattern. That way each time the template is
> used, it will have
> >it's own unique number that will be used to identify
> it. Does anyone know if
> >this is possible for Excel 2000?
> >
> >Thanks!
> >.
> >
>

[Paul B]

Natalie, you said you are using a template, .xlt and not a .xls file, if
that is the case you will need to use something like the examples here

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Natalie" <Natalie@discussions.microsoft.com> wrote in message
news:82149579-27A2-45C3-AB7F-450F8D579352@microsoft.com...
> Hello,
>
> Your suggestion didn't work for me. Can you help me figure out if I did
> something wrong? I did all the steps as you suggested, closed MS visual
> basic and returned to excel. When I reopened the file, it said that there
> was an error and would I like to debug. When I said yes, it took me to
> visual basic and highlighted the line:
> With sheets("Sheet1").[A1]
>
> Thinking that the problem is that I want to auto number cell A2, not A1, I
> changed the cell number and saved. This kept me from having messages
saying
> I need to debug, but didn't make the spreadsheet automatically number
itself
> each time it is opened.
>
> Thanks for any help you can offer!
>
> Natalie
>
> "Jason Morin" wrote:
>
> > You could add a 1 to Sheet1!A1 each time the workbook is
> > opened. Press ALT+F11, double-click the "ThisWorkbook"
> > module under View>Project Explorer and paste in:
> >
> > Sub Workbook_Open()
> > With Sheets("Sheet1").[A1]
> > .Value = .Value + 1
> > End With
> > ActiveWorkbook.Save
> > End Sub
> >
> > --
> > HTH
> > Jason
> > Atlanta, GA
> >
> >
> > >-----Original Message-----
> > >I created a template for a purchase order in Excel.
> > Everytime someone opens
> > >the template, I would like one cell to be automatically
> > numbered in a
> > >sequential pattern. That way each time the template is
> > used, it will have
> > >it's own unique number that will be used to identify
> > it. Does anyone know if
> > >this is possible for Excel 2000?
> > >
> > >Thanks!
> > >.
> > >
> >