How to use a UserForm: CheckBox Checked if Listbox column 5 text matches checkBox text
Category: [Excel] Demo Available
How to use a UserForm: CheckBox Checked if Listbox column 5 text matches checkBox text
Requirement:
The user has a UserForm with 39 checkBoxes that relate to different assessments that employees may need.
The user has a ListBox that imports the assessments currently held by the operative from the worksheet.
The assessments are in column 5 of the ListBox.
The name of the assessment matches the caption name of the CheckBoxes.
The user wants to know if there iss there a way of writing code to automatically checking the CheckBoxes if the caption matches the assessment listed in column 5 of the ListBox?
Solution:
Here's an example for the first method, which should be the easiest to understand. I don't know what event repopulates your listbox, but this code should be for that:
Code:
Private Sub SetCheckBoxes()
Dim cb As MSForms.Control, lb As MSForms.ListBox, n As Long
Set lb = Me.ListBox1
For Each cb In Me.Controls
If TypeName(cb) = "CheckBox" Then
cb.Value = False
For n = 0 To lb.ListCount - 1
If lb.List(n, 5) = cb.Caption Then
cb.Value = True
Exit For
End If
Next
End If
Next
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by JonathanVH.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to auto-generate unique ID in Excel VBA UserForm |
| How to calculate userform textbox and cell value for label caption |
| How to vertically centre text in userform label |
| How to clear Userform |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.