Auto Assign Numbers when Opening a Template

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132
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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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

Board Regular
Joined
Jul 14, 2004
Messages
132
But if it is a new form that is opened every time, how do I get it to read the last number?
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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.


Code:
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

Board Regular
Joined
Jul 14, 2004
Messages
132
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. :eek:
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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.
 

Forum statistics

Threads
1,171,885
Messages
5,878,041
Members
433,313
Latest member
Excelnoob69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top