Automatically generate order number and increment by 1 and post into worksheet.

Hi All,

I have created a userform which allows a user to fill in set fields which posts the data entered into the relevant cells in a worksheet in a separate workbook.

I am quite new at VBA coding and am struggling to find the relevant sources to help me create an order number that is automatically generated.

My desired result is that once all fields have been completed the user clicks a button and the following happens:

• Opens workbook and posts the data into the sheet
• Check the last order number and increment it by 1
• Add the new order number in the relevant cell in the relevant row with all other data
• Save workbook
• close workbook
• Quote order number in message box

I want all order numbers to start with "ATH -" then start "000000" and increment by 1 "000001, 0000002 and so on"

My worksheet currently looks like:

worksheet.png

And my VBA code is:

HTML Code: 

Hi LokeshKumar,

Thank you for your quick reply!

How would you quote that number in a msg box after it has saved the data to the worksheet and form has been unloaded?

Thank you

Hi,

I have two workbooks, one with the userform on it and the second to log all the data submitted by the userform (this is so multiple people can use the userform).

I want each userform entry to have a unique reference number in the format I advised (ATH000001 etc). I want the macro to check the last reference quoted and then increment it by 1.

Once the user has filled in all the details on the userform they click a "generate" button which the macro should then generate a new reference number, log that reference along with all other data entered into the userform and then quote that reference number in a message box. Then clear and unload the form for the next user.

Based on the code I had and the snippet you provided I have the form to do everything I have described but actually quote the number at the end.

I hope this makes sense

I have attached the two workbooks.

Hi LokeshKumar,

Thank you again for your reply.

The OrderNumber is a text field that the user manually enters this is (emptyRow, 7) in the code. The Authorisation number that I want to automatically generate, save a quote doesn't have a text field on the userform because it's not something the user enters.

The code you have provided seems to target the OrderNumber text field, not the Authorisaton number that gets generated and inputted in (emptyRow, 8).

I get an error "Invalid qualifier" when I use the added code.

The code you have provided:

Please Login or Register  to view this content.

Shouldn't this be inside the Msgbox so it is quoted?

Thank you for your continued help.