Using Check Box Control on Excel User Form

How to use a check-box with a list-box on an Excel user form
We learnt in the last 3 training videos how to use list-boxes and populate them with data using (a) a range, (b) an array and (c) a named range. Sometimes we may not have thought of all the options in the listbox. For example, you might offer many qualifications or locations in a list-box and the user doesn’t find the one he wishes to enter. At this point a check-box is very handy. When the check-box is activated or checked, we make a label and a new text-box visible. Here the user can enter, for example, the amount he spends every month. Now this data gets transferred to the Excel worksheet when we click on the command button.¬†Watch the video to see how the code needs to be changed in different places for the check-box to perform optimally:
The complete VBA code is given below:
Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Label5.Visible = True
TextBox2.Visible = True
End If
End Sub

Private Sub CommandButton1_Click()
Sheet1.Activate
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Text
Cells(erow, 2) = ListBox1.Value
Cells(erow, 3) = ListBox2.Value
If TextBox2.Visible = True Then
Cells(erow, 4) = TextBox2.Text
Else
Cells(erow, 4) = ListBox3.Value
End If
End Sub

Private Sub CommandButton2_Click()
TextBox1.Text = “”
CheckBox1 = False
If CheckBox1 = False Then
Label5.Visible = False
TextBox2.Visible = False
TextBox2.Text = “”
End If
End Sub

Private Sub CommandButton3_Click()
End
End Sub

Private Sub UserForm_Initialize()
Label5.Visible = False
TextBox2.Visible = False
ListBox2.List = Array(“Tata”, “Vodafone”, “Airtel”, “Other”)
End Sub

View the training video:


Further reading:
ActiveX Controls

Leave a Reply

Your email address will not be published. Required fields are marked *