Userform Vba code to subtrack and Generate new ID

0
down vote
favorite

I have created a simple UserForm to enter new equipment details to the Tooling list in the spreadsheet, form works fine except for one little thing, which is New Tool ID.

Basically what I need this for to do is once form is opened/called new Tool ID need to be created, which could be and Alfa numerical set of characters like AA-01234, AA-01235, AA-01236 and so on.

Also, is there a way of posting newly added Tool ID in the MsgBox along with MsgBox "One record added to Customers List. New Customer ID is "

All of my attempts to create this are failing and causing errors, which I really cannot figure out since I am new to VBA and had never used it until now.

Here is my code, Customer ID is TextBox1.

Thanks in advance

Code:

---

    Private Sub CommandButton1_Click()
  If Application.WorksheetFunction.CountIf(Range("A:A"), Me.TextBox1) > 0 Then
If MsgBox("Tool Name Already Exists.Do you want to continue?", vbQuestion + vbYesNo) <> vbYes Then
    Exit Sub
End If
End If

If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox4.Value = "" Or TextBox5.Value = "" Or TextBox6.Value = "" Then
If MsgBox("Form is not complete. Do you want to continue?", vbQuestion + vbYesNo) <> vbYes Then
Exit Sub
End If
End If
Dim ssheet As Worksheet

Set ssheet = ThisWorkbook.Sheets("2018")
nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

ssheet.Cells(nr, 1) = TextBox1.Value
ssheet.Cells(nr, 2) = TextBox2.Value
ssheet.Cells(nr, 3) = TextBox3.Value
ssheet.Cells(nr, 4) = TextBox4.Value
ssheet.Cells(nr, 5) = TextBox5.Value
ssheet.Cells(nr, 6) = TextBox6.Value
ssheet.Cells(nr, 7) = TextBox7.Value

Call resetForm

End Sub

Sub resetForm()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
Data_Entry.TextBox1.SetFocus

End Sub
Private Sub CommandButton2_Click()
TextBox1.Value = ""
TextBox2.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
Data_Entry.TextBox1.SetFocus
End Sub

Private Sub UserForm_Initialize()

Me.TextBox1 = Application.WorksheetFunction.Max(Range("A:A")) + 1

Me.TextBox3 = Date

End Sub

---

I wonder how can my code to pick up the last 3 or 4 number to generate new number.



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA