Excel Training

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. A little coding and the solution is found.
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





Reference

How to use a Check-box with a List-Box in an Excel user-form