March 27, 2015

Ensure All Data Fields in UserForm Filled Before Transfer to Excel Worksheet Using VBA

These are three of the questions asked by many of our YouTube channel and website visitors:
1. How to ensure that all fields in an Excel userform are completed before transferring data to a worksheet
2. How to clear all fields of a userform  and start on a clean slate for the next data entry
3. How to display all the data entered on a userform before transferring it to a worksheet

After creating a userform with a variety of fields like text-boxes, combo-box and list-box, we have coded a few command buttons with VBA to clear the data in the userform using a looping process, transfer the data from the userform to an Excel worksheet quickly and easily after verifying that all the data in the relevant userform fields have been filled, displaying the filled out data in another user-form in a multiline text-box and finally exiting from the macro or sub-routine. Therefore the complete process of transferring and displaying data from a user-form has been automated.
View the training video before you study the accompanying VBA code shown below:

Code for the command button with caption ‘Clear Data from Controls’:
Private Sub CommandButton1_Click()
End Sub

Macro code for ‘clearFormdata’:
Sub clearFormData()
Dim ctl As Control
For Each ctl In UserForm1.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Or TypeName(ctl) = “ListBox” Then
ctl.Value = “”
End If
Next ctl
End Sub

VBA or macro code for command button with caption ‘Check data in controls before transfer’:

Private Sub CommandButton2_Click()
If TextBox1.Text = “” Then
Cancel = 1
MsgBox “TextBox1 not complete”
Exit Sub
End If
If TextBox2.Text = “” Then
Cancel = 1
MsgBox “TextBox2 not complete”
Exit Sub
End If
If TextBox3.Text = “” Then
Cancel = 1
MsgBox “TextBox3 not complete”
Exit Sub
End If

If ComboBox1.ListIndex = -1 Then
Cancel = 1
MsgBox “ComboBox1 not complete”
Exit Sub
End If

If ListBox1.ListIndex = -1 Then
Cancel = 1
MsgBox “ListBox1 not complete”
Exit Sub
End If

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Text
Cells(erow, 2) = TextBox2.Text
Cells(erow, 3) = TextBox3.Text
Cells(erow, 4) = ComboBox1.Value
Cells(erow, 5) = ListBox1.Value

End Sub

Macro code for the command button with caption ‘Display Data’:
Private Sub CommandButton4_Click()
UserForm2.TextBox1.Text = UserForm1.TextBox1.Text & ” ” & UserForm1.TextBox2.Text & ” ” & UserForm1.TextBox3.Text

End Sub

Macro code when the UserForm1 initializes:
Private Sub UserForm_Initialize()

Me.ComboBox1.List = Array(“Manager”, “Staff”)

Me.ListBox1.List = Array(“New Delhi”, “New York”)

End Sub

VBA code for the ‘Exit’ command button on UserForm1:
Private Sub CommandButton3_Click()
Unload Me
End Sub

The above code is also used for the ‘Exit’ command button on UserForm2.

Multiline property of text-box in user-form2
Multiline property of text-box in user-form2

Download a sample file for practice:

Further Reading: Check that All Fields in a Userform are Completed before Submitting Data- excel vba

3 thoughts on “Ensure All Data Fields in UserForm Filled Before Transfer to Excel Worksheet Using VBA

  1. Hi sir.. Good Day how can i check if the data i inputed in userform is on the workbook that if he data inputed in txtbox exist in the excel sheet it will alloq the userform to transfer the data to excel but if the data inputed ia not on the list the usrform will tell me th the data inputed doesnt exist in the excel sheet.. please help

  2. Hi,
    I want to make like an inventory data for trailers for example:
    it will have trailer # the UPC of the item going in the trailer the item description how many are putting in how many taking out on hold/sold.
    then I want to be able to put the upc# or name and it will let me know how many I have and which trailer is in. and keep a record with a date when it was taken out or put in.

    I have seen a lot of videos of your training and I have tried to put everything together but is not working I’m able to do the form and have the data record it but can’t search or subtract.

    if you could help me that would be great.

    with your videos, I have been able to do attendance tracker, safety incentive, write up tracker that expire when it hits a specific date.

  3. Sir Good Evening,
    Sir i want enter in a user form textbox like 1 then display the name of person in the another user form textbox and that name come from worksheet.

Comments are closed.