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()
clearFormData
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”
TextBox1.SetFocus
Exit Sub
End If
If TextBox2.Text = “” Then
Cancel = 1
MsgBox “TextBox2 not complete”
TextBox2.SetFocus
Exit Sub
End If
If TextBox3.Text = “” Then
Cancel = 1
MsgBox “TextBox3 not complete”
TextBox3.SetFocus
Exit Sub
End If

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

If ListBox1.ListIndex = -1 Then
Cancel = 1
ListBox1.SetFocus
MsgBox “ListBox1 not complete”
ListBox1.SetFocus
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
UserForm2.Show

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.

Download a sample file for practice:

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