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.

 

[ZVI]

If Sheet2 is not active at running that macro then I'd suggest add .Worksheet. into this code line:
x = .Worksheet.Evaluate(...

And for that code it is assumed the sheet name is "Sheet2" and the serial numbers are in its A2 and the below cells, in which cells actually the serial numbers are?

 

[SSF1590]

This is my code

Private Sub UserForm_Initialize()
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")
End Sub

 

[Fluff]

Whilst ZVI has made a vary good point about making sure that the code is looking at the correct sheet, the only way I can get a "Application-defined or object-defined error." error is if there is no textbox on the userform that is called "TextBox1".

If you add a new line before "End Sub" and type in Me.T you should get a list of all the controls on the userform that begin with T, is Textbox1 listed?

 

[ZVI]

May be try also a bit modified code where "Sheet2" is replaced with 2 and the Evaluate formula ignore empty and not serial number values:

Sub test()
    Dim x As Long
    With Sheets(2).Range("A2", Sheets(2).Range("A" & Rows.Count).End(xlUp))
        x = .Worksheet.Evaluate("MAX(IF(ISNUMBER(--RIGHT(" & .Address & ",3)),--RIGHT(" & .Address & ",3)))")
    End With
    TextBox1 = "TARA-SS-" & Format(x + 1, "000")
    'Debug.Print TextBox1
End Sub

But as Fluff has already marked it is not for Application-defined or object-defined error

 

[SSF1590]

I added .Worksheet to that line in the code and change A2 to A4 which is the cell where th serial number starts. A4 has written TARA-SS-0001. As for "Sheet2" I changed it for "Database" as this is the name of the sheet where the serial numbers are, but the code is still not working, it is showing me the same exact error.

 

[SSF1590]

Hello,

Thank you very much fr your help on this. I follow your instructions and it is still giving me the same error. I checked and TextBox1 exist.

 

[SSF1590]

It is working now! You might laughed at me due to the silly mistake I had (x1Up) instead of (xlUp).

Also, what is the difference between the first code and the second one (this below). The second one will not look for empty cells?
Sub test()
Dim x As Long
With Sheets(2).Range("A2", Sheets(2).Range("A" & Rows.Count).End(xlUp))
x = .Worksheet.Evaluate("MAX(IF(ISNUMBER(--RIGHT(" & .Address & ",3)),--RIGHT(" & .Address & ",3)))")
End With
TextBox1 = "TARA-SS-" & Format(x + 1, "000")
'Debug.Print TextBox1
End Sub

 

[Fluff]

That's right, the code I supplied will fail if there are blank cells in the range, whilst ZVI's code won't.
Also ZVI's code will ignore any cells where the the last 3 digits are non-numeric.

 

[ZVI]

According to the post 15 it seems that there are 4 digits in the serial number "A4 has written TARA-SS-0001", if so then replace 3 by 4 in the code. and use "0000" in the result formatting. As to the code - the initial Fluf's version is faster and applicable for the posted data.

 

[SSF1590]

Thank you both for your help and patience, the codes provided work perfectly.