Userform Vba code to subtrack and Generate new ID

**Thainguyen** · 06-27-2018, 01:28 AM

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