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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought 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,

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.