Creating a "ticket number" with VBA

New to VBA and this forum.

I am creating a userform to allow multiple users to fill out an excel spreadsheet to track IT-related issues. Each record gets a unique "ticket number" which is made up of the date in yyyymmdd format plus a letter (these tickets are currently created by a single user who manually assigns the ticket number using this numbering scheme). The first "ticket" created on a given day is assigned the letter 'A' (20110523A), the second is assigned letter 'B' (20110523B) and so on. The first ticket tomorrow would be 20110524A and so on.

Since each user may be submitting a ticket independent of each other, I would like to automate the ticket numbering process so that excel will create the ticket number when the ticket is submitted. Ideally, after the ticket is submitted, a message box would pop up that says something like "The ticket number assigned to this issue is '20110523B'." The date portion of the ticket number seems easy enough but is there a way to have excel look at existing records and determine what letter should be assigned to the ticket?

Can someone help me with the VBA code needed?

Wanted to change it just a little bit. If you want to see how it works just run the code on a blank worksheet.

Please Login or Register  to view this content.

Hi Stnkynts,
I tried your code below and it works. However, anytime two users log in at the same time, both get the same ticket number. Please how can i resolve this issue.

Originally Posted by stnkynts

Here is what i came up with. I used a command button to generate the ticket number.

I assumed that the ticket numbers are stored in column A. This can be edited by changing the myRange line.

Please Login or Register  to view this content.