How to create Data Entry form in excel vba

Data Entry Userform

Option Explicit
Private Sub CommandButton1_Click()
' 1st define your worksheet
Dim wh As Worksheet
Set wh = Sheets("Sheet2")
Dim i As Long
i = wh.Range("B" & Rows.Count).End(xlUp).Row + 1
' transfer data to worksheet from userform
wh.Cells(i, 1) = Me.TextBox1.Value
wh.Cells(i, 2) = Me.TextBox2.Value
wh.Cells(i, 3) = Me.TextBox3.Value
wh.Cells(i, 4) = Me.TextBox4.Value
' crate msgbox after data transfer
MsgBox "Data send Successfully"
' now clear the textboxes after data transfer
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
End Sub

If You want to create data entry userform then follow the below steps

Step1. If you don't have Developer Tab in your excel file then You need add Developer Tab from customize ribbon
So go to file >>>> option >>>> customize ribbon >>> find out Developer Tab from the List right side >>> click on check box for Developer >>> Ok


Now you will see Developer Tab in menu bar.
Step2. go to Developer Tab 

Step3. Visual Basic


Step4. insert userform


Step5. You will see a ToolBox from where you need to design your userfrom

Step6. Double Click on SAVE button
and write the above code
where first of all you need to define your worksheet i.e your working worksheet as follow..

Dim wh As Worksheet
Set wh = Sheets("Sheet2")

Next you need to select or find out where you want to transfer your data i.e find out last non empty row for selected column ( here I select B column ) and add  (+1).

Dim i As Long
i = wh.Range("B" & Rows.Count).End(xlUp).Row + 1

Now transfer data from userform to worksheet as follow 

wh.Cells(i, 1) = Me.TextBox1.Value
wh.Cells(i, 2) = Me.TextBox2.Value
wh.Cells(i, 3) = Me.TextBox3.Value
wh.Cells(i, 4) = Me.TextBox4.Value

Where wh.cells(i, 1) means A column and i.e 1 = A column
                                                                         2 = B column
                                                                         3 = C column
                                                                         4 = D column
i.e Data will transfer from TextBox1 to A column
                                           TextBox2 to B column
etc.

And a massage box will show on your screen after data transfer 

MsgBox "Data send Successfully"

Now Clear the TextBoxes as follow

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""

Where "" means blank

Thank you for showing interest on my blogspot
Please subscribe my channel

1 comment: