Welcome to Excel Avon
DATA ENTRY BY USERFORM
DOWNLOAD USED EXCEL FILE FROM HERE>>
Visual Basic is an excellent language for automating repetitive tasks in Excel. Imagine taking your automation up a notch by creating highly functional Userform that also look clean for end users. Data Entry Form is a form that helps to enter the data with the help of a form in which the data can be added, searched, and previous data can be deleted. Data entry in Excel without forms can be very time-consuming.
Data entry by Userform in Excel helps overcome all these limitations and makes the task of entering data less time-consuming and less error-prone. In this article, let us see how to do data entry By UserForm using Excel VBA.
DATA ENTRY BY USERFORM USING EXCEL VBA
Well, you should know how to create a Data Entry by UserForm, because in the previous article we introduced you to UserForm and told you how to insert UserForm in VBE. And how to insert label and text box in it and let’s understand. For how to do data entry by UserForm, we have to go like last time, first go to Developer Tab then click on Visual Basic’s Option as shown in the image below.
On opening in VBE, you have to go to Insert and then UserForm has to be inserted, as can be seen in the image.
When we click on the UserForm, a separate wizard box will be created in a window VBE, in which you can see UserForm1 as the default name.
Now let’s make some changes like first of all we will change the name and caption of the UserForm1.
Now we will select the label from the Toolbox and then insert the label into the UserForm.
Drag the label, we will insert as many labels as there are properties. After inserting the label in UserForm we will name the label as.
Now we will insert the text box in the UserForm,
Click textbox and change the name of the text box for Name Column. Similarly, we will change the name of all the text boxes.
Now we’ll add a command button in UserForm, with this we Rename Command Button ‘Submit‘ and ‘Close‘.
Double click on close button, Double clicks on close button then we will create code for close button unload.me will use.
Private Sub CommandButton2_Click() Unload Me End Sub
Double click on Submit button. and Define Variables.
Private Sub CommandButton1_Click() Dim WS As Worksheet Dim lr As Long End Sub
Set worksheet as Activesheet, here we’ll Declare Last row.
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
End Sub
Now we will create the range to fill the names in the worksheet.
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
End Sub
Copy and paste Text Boxes code. and change cell.
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtName.Value
WS.Range("C" & lr).Value = Me.txtName.Value
WS.Range("D" & lr).Value = Me.txtName.Value
WS.Range("E" & lr).Value = Me.txtName.Value
End Sub
And then Change Text Boxes Name.
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtDOB.Value
WS.Range("C" & lr).Value = Me.txtGender.Value
WS.Range("D" & lr).Value = Me.txtPhone.Value
WS.Range("E" & lr).Value = Me.txtEmail.Value
End Sub
We will write the code to clear the textbox once the value is filled to worksheet. copy and paste that code and change text boxes names.
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtDOB.Value
WS.Range("C" & lr).Value = Me.txtGender.Value
WS.Range("D" & lr).Value = Me.txtPhone.Value
WS.Range("E" & lr).Value = Me.txtEmail.Value
Me.txtName.Value = ""
Me.txtDOB.Value = ""
Me.txtGender.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
End Sub
Now we will use the message box ‘Data Submit Successfully’ with VbInformation sign.
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim lr As Long
set ws = Activesheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtDOB.Value
WS.Range("C" & lr).Value = Me.txtGender.Value
WS.Range("D" & lr).Value = Me.txtPhone.Value
WS.Range("E" & lr).Value = Me.txtEmail.Value
Me.txtName.Value = ""
Me.txtDOB.Value = ""
Me.txtGender.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
MsgBox "Data submitted successfully!", vbInformation
End Sub
Go to the Activesheet. And Click on Insert Icon Select a button. Drag on Worksheet .
We have not yet created the macro which will show the Dataform then now we will go to VBE to create the macro then firstly go to the insert tab then insert the module. do data entry by UserForm with Excel VBA.
Once the module is inserted, and we’ll write a subroutine to Sub Open Form. Macro has been created which will only show the data form in the sheet.
Sub OpenForm() DataForm.Show End Sub
Now open worksheet. and we’ll assign Macro. Select macro and click Ok.
Now click on the insert button. The data form has been opened, now we will fill the information in it.
First text box is for Name, so we’ll write the name. Similarly fill all the data. and click submit button.
We will get the message of Data submitted Successfully.
Now Close UserForm.
Now you can see data is filled in Worksheet.
So now we want to make a condition that if the name is not written in the form, then this form should not be submitted. Go to VBE and click. create a code here.
Private Sub CommandButton1_Click()
Dim WS As Worksheet
Dim lr As Long
If Me.txtName.Value = "" Then
MsgBox "Please write the name", vbExclamation
Exit Sub
End If
Set WS = ActiveSheet
lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
WS.Range("A" & lr).Value = Me.txtName.Value
WS.Range("B" & lr).Value = Me.txtDOB.Value
WS.Range("C" & lr).Value = Me.txtGender.Value
WS.Range("D" & lr).Value = Me.txtPhone.Value
WS.Range("E" & lr).Value = Me.txtEmail.Value
Me.txtName.Value = ""
Me.txtDOB.Value = ""
Me.txtGender.Value = ""
Me.txtPhone.Value = ""
Me.txtEmail.Value = ""
MsgBox "Data submitted successfully!", vbInformation
End Sub
After writing the code we will go back to the worksheet. and then click on enter data button..
Now if we try to submit it, it will give us a message ‘Please write the name‘.
Now we will fill the information in the UserForm and submit it, even then it will give us a message which will be like this ‘Data submitted successfully.’
So now we will submit the Dataform then we will get this message.
Now you can see data is filled in Worksheet.
Therefore, I hope that you have understood How to Data Entry by UserForm Using Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to Data Entry by UserForm Using Excel VBA
