Auto Assign Numbers when Opening a Template

[cdnqte]

I am creating a work order template that I would like to automatically assign a work order number when a new one is opened. Is there a way to have this read another file to select consective numbers each time the template is used to open a new file?

 

[Oorang]

Yep
You can do it with a text stream or the Cheap way
Cheap way as is follows:
Read the last number... Make the next number the last number plus one.

 

[cdnqte]

But if it is a new form that is opened every time, how do I get it to read the last number?

 

[Oorang]

Ahhhh...
Well actually I like your solution. Put a file on a shared drive, and just pull the information and then mark off the entry. But depending on how fast your network drive is tha may create some slowdown. But here is some code to play with. This assume your ordernumber sheet has order numbers in column A and column b will be marked with "Used" and eachnumber is used. Important:A1 and B1 should be column headers.

Sub GetOrdernumber()
Dim FN As String
Dim Ordernumber As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Workbooks.Open Filename:="S:\My FOlder\Book1.xls"
FN = ActiveWorkbook.Name
Ordernumber = Range("B65536").End(xlUp).Offset(1, -1).Value
Range("B65536").End(xlUp).Offset(1, 0) = "Used"
ActiveWorkbook.Save
ActiveWindow.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox Ordernumber
End Sub

 

[cdnqte]

You have (almost) saved my sanity!! The code worked well and does almost what I need it to do! Instead of there being a message box, is it possible to have it drop the number into a specific cell? I am really new to VB so I have no idea how to do this!! I tried recording a macro and reading through the code but that didn't help me much.

 

[Oorang]

To drop it into a specific cell you need only to specify the cell.
Range("a1") = Ordernumber

Some things to read through in the VBE helpfile would be offset, and end.

If I have everything in a flat file database (that is column headers are in row 1, every ro is a record, and the primary key is column A) I like to use this to add info to the bottom record.

Range("A65536").end(XLUP).offset(1,0) = OrderNumber

This says: from Cell A65536 do ctrl-up (which should take you to the bottommost record.) Then go down (offset) 1 row. Edn produc- you are on the bottom record.

There are several different ways to land on a range, this is just my personal favorite.

 

[cdnqte]

PERFECT!!!

Thank you so much for the help and the tips!!!