Excel Training

Transfer Data from UserForm to Excel Worksheet

Userforms provide a great way to enter data and perform calculations and the data can easily be transferred to the Excel worksheet. The process, however, involves good programming knowledge of Visual Basic for Applications. Once the macro is written the data can be entered in the form. Because most of us are used to entering data in forms, the data entry process is natural and easier if the number of headers crosses a certain limit. You create the same number of text boxes on a form with relevant labels so that data can then be transferred from the userform onto the Excel worksheet. The first step after the creation of the form involves defining a few variables, then finding an empty appropriate row where the data will be filled, copying the data into the Excel cells and finally removing or clearing the data on the form to make way for the next entry. You may also provide for a process or system where the data entry is 'finished' and you can exit the system. We have created such a simple form for making data entries for employees of a company. You might observe some dates as numbers. These can, however, be easily corrected through proper formatting.

The complete VBA code for the command buttons:

Private Sub CommandButton1_Click()
Dim erow As Integer
Dim ws As Worksheet
Set ws = Worksheets("Data")

erow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


ws.Cells(erow, 1).Value = Me.TextBox1.Value
ws.Cells(erow, 2).Value = Me.TextBox2.Value
ws.Cells(erow, 3).Value = Me.TextBox3.Value 'For date formatting: Cells(erow, 3).Value = Format(Date, "mm/dd/yy")
ws.Cells(erow, 4).Value = Me.TextBox4.Value

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

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

The training video demonstrates the idea elaborately.

Excel Training