Auto generation of serial number in user form

[SSF1590]

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.

 

[Fluff]

Hi & welcome to MrExcel.
How about

    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]

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]

In what way doesn't it work?
Do you get any error messages?
Do you get the wrong value?

 

[SSF1590]

Sorry for not being more specific.

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

 

[Fluff]

You need to change the name of the textbox to match the name of your textbox.

 

[SSF1590]

My textbox is the same. TextBox1, but it is not working and it still shows me the same error.

 

[Fluff]

Did you put the code in the userform initialize event?

 

[SSF1590]

Yes I did, from the beginning, but still showing the same error.

 

[Fluff]

Can you post your current code?