September 3, 2019

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

2 thoughts on “Make Super Fast Data Entries Automatically

  1. Thank you very much sir for making the above video on my request. code is working fine sir. But month increments every time i click transfer button.
    Actually month should increment at the the end of the month only. Sir i have made some changes to the code like, 1) i have initialized id
    (KN-ST-00001) in userform initialize with code “KN-ST-” & FORMAT (1,”00000″) and in transfer button code i have given
    txt_Tid1=”KN-ST-” &FORMAT(VAL(RIGHT(txt_Tid1)+1),”00000″). This code increments id upto KN-ST-00010 and becomes KN-ST-000001.
    How to correct the code sir to increment continuously.
    sir i have made the program entirely just by watching your videos.
    I am great fan of your videos sir,

  2. Nice day to you sir.
    I am one of excel vba learner.
    Really your training materials help me lot to develop our in-house application.
    Thanks for your continual support sir.

Comments are closed.