Auto generating work order number

**Shmoedoe** · 07-02-2008, 08:45 AM

I am using Excel to produce work orders and I need each one to have its own unique number. Much like an invoice or contract number, each time I open the file I would like Excel to generate a new number for that spreadsheet with a value of 1 more than the previous spreadsheet. Does anyone know if this is possible?

**gunrgraham** · 07-02-2008, 03:19 PM

There's a question here about knowing what the new work order number is.

How would anybody - you, me or excel; know the number of the previous work order?

For example - if you're creating work order 52. How would we know that work order 51 exists? Do you file them in a particular directory on your PC or is there some sort of central log?

If there's no system like this, it's very hard for anyone to generate a new work order number.

Let me know.

**shg** · 07-02-2008, 03:31 PM

This is frequently asked, and there are several ways to do it.

If numbers are assigned only on your computer,
You can create a registry key that you increment each time you need a new number.
You can use an ad-hoc add-in that stores the number on a hidden sheet.

**Shmoedoe** · 07-02-2008, 04:12 PM

Shg,

Yes, that's what I would like to do. Please tell me how. I have a little experience with Excel but my knowledge is not that broad.

**shg** · 07-02-2008, 04:27 PM

Which one?

**Shmoedoe** · 07-02-2008, 04:37 PM

The easiest. Although I may have to have multiple users use the same spreadsheet, I would like to figure out how to do it on my own system first.

**shg** · 07-02-2008, 04:46 PM

This goes in a code module:

Function NextWorkOrder() As Long
    Const sAPP  As String = "Excel"
    Const sSEC  As String = "WorkOrder"
    Const sKEY  As String = "WorkOrderNum"
    Const nDEF  As Long = 0

    NextWorkOrder = GetSetting(sAPP, sSEC, sKEY, nDEF) + 1
    SaveSetting sAPP, sSEC, sKEY, NextWorkOrder
End Function

This goes in the ThisWorkbook module for the workorder template:

Private Sub Workbook_Open()
    Range("A1") = Format(NextWorkOrder, "000000")
End Sub

Change the range and formatting as you wish.

**Shmoedoe** · 07-02-2008, 05:56 PM

Once again, my knowledge about Excel is limited. How do I go about setting up a module?

**shg** · 07-02-2008, 06:10 PM

Open the xls file that contains the workorder template (I'm assuming this is an xls file).

For the first set of code,

1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor
3. From the menu bar, do Insert > Module
4. Paste the code in the window that opens

Then for the short routine,

1. Copy the code from the post
2. In Excel, right-click on the Excel logo in the upper-left corner and select View Code
3. Paste the code in the window that opens

Then from Excel, do File > Save as, select Template (*.xlt) from the drop-down at the bottom of the window, give it an appropriate name (like WorkOrder) and save in a place you can find later.

When you want to create a new work order, navigate to the directory and double-click the template.

**Shmoedoe** · 07-02-2008, 06:28 PM

Can't seem to get it to work. Would it be possible for you to attach the file to this thread?

**shg** · 07-02-2008, 06:39 PM

See attached.

**Shmoedoe** · 07-03-2008, 07:51 AM

GOT IT! Thanks a lot!

**shg** · 07-03-2008, 11:41 AM

You're welcome, glad it worked for you.

**Shmoedoe** · 07-15-2008, 09:13 AM

Now that everything works, is there a way to "clear the ticker?" For example, if I have run up the numbers and need to resort to a previous number, is there a way to do this? If I try to recreate the file by using an earlier number as the starting point, it seems to always continue from where it left off previously. Is there a file somewhere that is keeping track of the number that I am unable to get wrid of?

**Shmoedoe** · 07-15-2008, 09:27 AM

Before I used to open the template created and it would generate a new number. I would then save it to an *.xls file and it would keep the number generated. If I would open that *.xls file later, it would have the same number. However, now I have run into a problem where everytime I open the *.xls file it is generating a new number. It is as though the *.xls is acting as if it were my template.

I am not sure if this fact has any effect, but, I have the template located on the server and a link on my desktop to that file. I have been emailing the *.xls file to coworkers and they have been opening it. I was shocked to see when I opened the template one day that the number had increased by 10. Is their opening the *.xls file on their system also increasing the number?