We describe a looping process to automatically check whether all Textboxes Combo Boxes & List Boxes are completed before sending data to an Excel Worksheet in this post. Last time we had described a similar post where each textbox, combobox and list-box had to be coded individually and the process could be painfully long. The new automation with a ‘for next’ looping is extremely efficient.
In this new strategy to check whether all the fields in the user-form have been duly filled, we use a check button. After confirmation that all the fields have been properly completed in the user-form, we then transfer the data to the relevant cells in the Excel worksheet using a ‘transfer’ command button.
Before you study the VBA code to perform such an automation, watch the training video below:

Watch the video on YouTube.

Code for the ‘check’ command button:
Private Sub CommandButton1_Click()
Dim intTextBox As Integer
For intTextBox = 1 To 2
If Controls(“TextBox” & intTextBox) = “” Then
MsgBox “TextBox” & intTextBox & ” blank. Please enter relevant data!”
Exit For
End If
Next intTextBox
Dim intComboBox As Integer
For intComboBox = 1 To 2
If Controls(“ComboBox” & intComboBox).ListIndex = -1 Then
MsgBox “ComboBox” & intComboBox & ” blank. Please select data!”
Exit For
End If
Next intComboBox

Dim intListBox As Integer
For intListBox = 1 To 2
If Controls(“ListBox” & intListBox).ListIndex = -1 Then
MsgBox “ListBox” & intListBox & ” blank. Please select data!”
Exit For
End If
Next intListBox

End Sub

VBA code for the ‘Transfer’ command button:
Private Sub CommandButton3_Click()
Dim erow as Long
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) = ComboBox1.Value
Cells(erow, 4) = ComboBox2.Value
Cells(erow, 5) = ListBox1.Value
Cells(erow, 6) = ListBox2.Value

End Sub

VBA or macro code for the ‘Exit’ command button:
Private Sub CommandButton2_Click()
Unload Me
End Sub

Macro code for initialization of the user-form to populate combo-boxes and list-boxes:
Private Sub UserForm_Initialize()
ComboBox1.List = Array(“New Delhi”, “New York”, “London”)
ComboBox2.List = Array(“123456”, “123678”, “567890”)
ListBox1.List = Array(“VP”, “GM”, “Manager”, “Staff”)
ListBox2.List = Array(“January”, “February”, “March”, “June”, “August”, “October”, “Decemeber”)

End Sub

Download a sample Excel file: