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:
I get an error when I enter the exact same code to loop through text and combo boxes.
“Run-time error’-214024809 (80070057)’:
Could not find the specified object”
If Controls(“TextBox” & intTextBox) = “” Then
Amazing Trainng Sir……
I am a learner and learn by your videos
But much better if sample file I may collect.