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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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")
 
Upvote 0
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!
 
Upvote 0
In what way doesn't it work?
Do you get any error messages?
Do you get the wrong value?
 
Upvote 0
Sorry for not being more specific.

It gives me error "1004"
Application-defined or object-defined error.
 
Upvote 0
You need to change the name of the textbox to match the name of your textbox.
 
Upvote 0
My textbox is the same. TextBox1, but it is not working and it still shows me the same error.
 
Upvote 0
Did you put the code in the userform initialize event?
 
Upvote 0
Can you post your current code?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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
Back
Top