Below is a dataset of several students’ names. Of these students, only 3 passed. We want to create a drop-down checklist containing these students’ names. Then, we want to check the passed students’ names and get an output in another cell containing only the passed students.
Step 1: Create Drop-Down Checklist Options
- Click on the Developer tab on your Excel ribbon.
- Click on the Insert tool >> ActiveX Controls group >> List Box (ActiveX Control) option.
- A list box will open.
- Drag your mouse to determine the list box area.
- Right-click on the list box area and choose the Properties option from the context menu.
- A Properties window will appear.
- Enter checkList in the (Name) text box.
- Refer to the cells B5:B12 in the ListFillRange text box.
- Choose option 1 – fmListStyleOption from the ListStyle option list.
- Choose option 1 – fmMultiSelectMulti from the MultiSelect option list.
- There will be a drop-down checkbox list with the student’s names.
- To get the passed students’ names in a cell, create a header and name the output cell as CheckListOuput in the NameBox.
Your dropdown checklist options are created properly and the output cell is also declared with a proper name.
Read More: How to Make a Daily Checklist in Excel
Step 2: Add an Interactive Button to Extract the Result
Add a button to make a checklist interactive and extract your desired result.
- Go to the Insert tab.
- Go to the Illustrations group >> Shapes tool >> Rectangle option.
- You will have control over a rectangle.
- Drag your mouse to create your button area and fill the rectangle with your desired color.
- Right-click on your mouse inside the rectangle area and choose the  Assign Macro… option from the context menu.
- The Assign Macro window will appear.
- Name your macro as Button_Click on the Macro Name: option and click on the New button.
- The VB Editor will open, creating a new module named Module1.
- Enter the following VBA code:
Sub Button_Click()
Dim buttonShape As Shape, listOption As Variant, M, N As Integer
Dim xP As String
Set buttonShape = ActiveSheet.Shapes(Application.Caller)
Set checkListBox = ActiveSheet.checkList
If checkListBox.Visible = False Then
checkListBox.Visible = True
buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students"
resultStr = ""
resultStr = Range("CheckListOutput").Value
If resultStr <> "" Then
resultArr = Split(resultStr, ";")
For M = checkListBox.ListCount - 1 To 0 Step -1
xP = checkListBox.List(M)
For N = 0 To UBound(resultArr)
If resultArr(N) = xP Then
checkListBox.Selected(M) = True
Exit For
End If
Next
Next M
End If
Else
checkListBox.Visible = False
buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here"
For M = checkListBox.ListCount - 1 To 0 Step -1
If checkListBox.Selected(M) = True Then
listOption = checkListBox.List(M) & ";" & listOption
End If
Next M
If listOption <> "" Then
Range("CheckListOutput") = Mid(listOption, 1, Len(listOption) - 1)
Else
Range("CheckListOutput") = ""
End If
End If
End Sub
Note: In the code, the button’s macro name is Button_Click; checkList is the name of our checklist, and checkListOutput is the output cell’s name. You must change these names inside the VBA code if you name these things something else.
- Afterward, press Ctrl + S on your keyboard.
- As a result, a Microsoft Excel window will appear.
- Following, click on the No button.
- The Save As dialogue box will appear.
- Choose the Save as type: option as .xlsm file and click on the Save button.
- The code is saved and workable now.
- Close the code window and go back to your main Excel file.
- There will be interactive checkboxes and the Tick the Passed Students button.
- Click on the passed students’ names Lily, Mathew, and Naomi.
- Click on the Tick the Passed Students button.
- You will find your result in cell F5 and the button will be named Click Here now interactively.
You will be able to create a drop-down checklist in Excel and use it to generate results.
Read More: How to Create an Audit Checklist in Excel
Download the Practice Workbook
You can download our practice workbook from here!
Relative Articles
- How to Make Checklist with Conditional Formatting in Excel
- How to Make a Checklist in Excel Without Developer Tab
- How to Create an Interactive Checklist in Excel