Auto Sequential numbering on form | VBA & Macros | Excel Forum • My Online Training Hub

Auto Sequential numbering on form

ToniW

New Member

Members

Forum Posts: 1

Member Since:
July 28, 2016

Offline

July 28, 2016 - 2:12 am

I have a job work order that I need to have auto number sequentially every time it is opened. The file is a shared file, and could be opened by more than one person at a time.

The number system we use is two digits for the year, then the work order number. 16-1. I need the number after the dash to change automatically.

I have attached a copy of the workbook. Thank you in advance.

Philip Treacy

Admin

Forum Posts: 109

Member Since:
October 5, 2010

Offline

July 29, 2016 - 1:21 pm

Hi Toni,

Here's a workbook you can use as a template. When it is opened it creates a new workbook with the next Job Work Order number in it.

The JWO is saved in the template and each time it is opened, the JWO is incremented.

The initial value for the JWO is controlled by the value in G1. So set that to whatever number you want to start from.

The new workbook is automatically saved with a filename like this : Job_Work_Order_16-1.xlsx. By modifying some parameters in the code you can change how the filename looks, or you can turn off saving completely if you like.

When the user opens the workbook, macros must be enabled so that the code can run. To avoid this 'enable macros' warning each time, store the workbook in a Trusted Location.

The code for this is based off the code I wrote for Macro Enabled Templates

Have a look through the code which is in the This_Workbook module and let me know if you have any questions.

Regards

Phil