Make Super Fast Data Entries Automatically

How to make super fast data entries automatically using VBA. Here’s the user query:

I am working on an inventory project where I have a text box for ‘ID’. ID is of the format
ST-AUG-0001. I wish to increment the month from AUG to SEP to NOV etc after each month and also increment sequence 0001 to 0002….every time I click on transfer button in user form. Please explain with a video. Thank you in advance.

Here’s the complete VBA code:

Option Explicit

Private Sub CommandButton1_Click()

Dim mynum As Long
Dim erow As Long, lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
erow = lastrow + 1

If TextBox1.Text Like “[A-Z][A-Z]-[A-Z][A-Z][A-Z]-####” Then
MsgBox “Valid ID entry”
Range(“A” & erow) = TextBox1.Text
ElseIf TextBox1.Text Like “[A-Z][A-Z]-[A-Z][A-Z][A-Z]-#####” Then
MsgBox “Valid ID entry”
Range(“A” & erow) = TextBox1.Text
ElseIf TextBox1.Text Like “[A-Z][A-Z]-[A-Z][A-Z][A-Z]-######” Then
MsgBox “Valid ID entry”
Range(“A” & erow) = TextBox1.Text
Else
MsgBox “Invalid ID entry”
Exit Sub
End If

If Len(TextBox1) = 11 Then
mynum = Val(Right(TextBox1, 1))
mynum = mynum + 1
ElseIf Len(TextBox1) = 12 Then
mynum = Val(Right(TextBox1, 2))
mynum = mynum + 1
ElseIf Len(TextBox1) = 13 Then
mynum = Val(Right(TextBox1, 3))
mynum = mynum + 1

End If

TextBox1 = 1 & “-” & Mid(TextBox1, 4, 3) & “-” & Year(Date)

TextBox1 = Val(Month(TextBox1)) + 1

If TextBox1 > 12 Then
TextBox1 = 1 & “-” & “JAN-” & Year(Date) + 1
TextBox1 = Val(Month(TextBox1))
End If

TextBox1 = “ST-” & UCase(MonthName(TextBox1, 3)) & “-” & “000” & mynum

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Option Explicit

Sub showuserform()
UserForm1.Show
End Sub