self generate a running number

[latecomer]

Hello
is there a way in excel 2000 to generate a running number? eg a unique (in running order) number for purchase order form

[Tom Urtis]

There are many ways to do this, depending on what event you prefer that will generate a unique number.

Here is an example of a unique number being placed into cell A1 of Sheet1, by incrementing whatever number is in there by 1 each time the workbook opens.

Locate the Excel workbook icon that is just to the left of the "File" worksheet menu option, near the upper left corner of your screen. Right click on that icon, then left click on View Code, and paste this in:

Private Sub Workbook_Open()
Sheet1.[A1] = Sheet1.[A1] + 1
End Sub

Modify for sheet and cell reference.

This same effect can be accomplished by other means, such as whenever a sheet is activated, or the invoice gets printed, or a certain cell is filled with a certain value, etc. This example though, should get you started.

Tom Urtis

[latecomer]

thanks Tom. much appreciated. do u think I can add a text to the number? eg 001/it/md.

[Tom Urtis]

Yes this can be done, and here is what I would recommend for your situation. My experience has been that most people find that it is easier to edit formulas than to edit VBA code. At least with a formula, if edited incorrectly, the worst case outcome will be a cell or cells that have error displays or incorrect calculations. Not a good scenario, but still not as bad as a VBA code that is edited incorrectly, which may stop an entire workbook in its tracks. And, there are only 3 cells totally involved here.

So, I told you that story to tell you this one:

Consider this suggestion -- replace the Workbook_Open code I posted originally with this instead:

Private Sub Workbook_Open()
Sheet1.[A1] = _
Sheet1.[B1] & _
"/" & _
Sheet1.[C1]
End Sub

Remember, right click on the Excel workbook icon to the left of the File menu, then left click on View Code, and paste this in.

Now, in cell B1, enter the formula
=TEXT(LEFT(A1,3)+1,"000")

And in C1 enter the formula
=RIGHT(A1,LEN(A1)-SEARCH("/",A1))

And to get off on the right foot, in cell A1 of Sheet1, enter the value
001/it/md

The reason for all this effort at the outset is based in my belief (and I could be mistaken but this is my experience) that having the formulas determine what you want displayed, instead of the VBA code, will be easier to maintain for most people. All this stuff can be wrapped up in VBA code, but when someone besides you must edit this file, changing the formulas might be more intuitive for them.

Sorry for this long-winded answer, but your solution involved leading zeros, with incrementing numbers combined with text in one string. I took your reply to mean that the first 3 characters of the cell will be numerals that should increase by 1 each time the workbook opens.

You can modify the code by referncing cells other than B1 or C1, depending on where you want toplace those formulas, preferably out of sight and protected.

Hope this helps.

Tom Urtis

[WhatEverInMind]

Hi Tom,
Basically i'm using excel 2007. If i want to use the same code to produce my purchase order, is there any problem for that? And how to input the code into it?