Auto generation of serial number in user form

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
I have a database in sheet 2 with certain serial numbers and a userform in sheet 1 that allows me to manage this database, including adding new entries. Each entry needs to have a unique serial number. The serial number can not be duplicated. The serial number follows this format TARA-SS-001. I need the last three digits to increment by one based on my last serial number in my database in sheet 2. For example, if my last serial number is TARA-SS-001 in my data base my next serial number should be TARA-SS-002. Upon opening the userform, the serial number should appear on Textbox1 prefilled. I just cannot come with any code that can help me on this, I am pretty new to VBA. Can someone pease help me to find a code?

Thank you very much.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
    Dim x As Long
    With Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
        x = Evaluate("max(--right(" & .Address & ",3))")
    End With
    TextBox1.Value = "TARA-SS-" & Format(x + 1, "000")
 

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
Hi! Thank you very much for your quick reply on this. I really appreciate it and thank you for welcoming to this amazing forum.

I tried to run the code but it not working. I have double checked it and everything is the same as the code you kindly provided me with. What else can I do? The SS on the serial number can be change fro the last digit of the current year we are if that makes a difference.

Again, thank you very much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
In what way doesn't it work?
Do you get any error messages?
Do you get the wrong value?
 

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
Sorry for not being more specific.

It gives me error "1004"
Application-defined or object-defined error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
You need to change the name of the textbox to match the name of your textbox.
 

SSF1590

Board Regular
Joined
Oct 20, 2019
Messages
73
My textbox is the same. TextBox1, but it is not working and it still shows me the same error.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
Did you put the code in the userform initialize event?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,719
Office Version
365
Platform
Windows
Can you post your current code?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,142
Messages
5,466,921
Members
406,509
Latest member
David504

This Week's Hot Topics

Top