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 As Integer, 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
hi, to me the VBA code is not working and in the debugging I first get stuck here
Else
Range(“CheckListOutput”) = “”
and by ignoring and re-clicking I get as top here
resultStr = Range(“CheckListOutput”).Value
It would be really helpful for me to get it work. thanks!
Hello Claudia Sgiarovello,
On our end the VBA code is working perfectly. Rechecked the code to confirm it.
Make sure you replace the curly quotation marks with straight quotation marks to avoid syntax errors in VBA. If the problem persists, double-check the name of the range “CheckListOutput” to ensure it matches exactly with what is in your Excel workbook.
1. Ensure all quotes are straight quotes (“).
2. Check that “CheckListOutput” matches the actual named range in your Excel workbook.
3. Verify that “checkList” matches the name of your ActiveX listbox control.
If these steps don’t resolve the issue, consider providing more details about any specific error messages or behaviors you’re encountering.
Regards
ExcelDemy
I have the same issue however i only used for single option, it doesnt work
Hello Jennifer Almeida,
In our existing VBA code, the previously stored values in the output cell are overwritten when a new selection is made. This happens during the Button_Click event when the new selection is written directly into CheckListOutput, replacing the previous value.
To preserve previous selections, we adjusted the logic so that the newly selected value is appended to the existing content rather than overwriting it. Here’s an update to your code:
Regards
ExcelDemy
The append function only seems to work down the list;however, if you try to check off an item earlier in the list, it will not append. How can we tweak the code to make it append in any direction?
For some reason I cannot change the name of the button. I’ve gone through the code and changed everywhere that says “tick the passed student” and changed it to the verbiage I need the button to say. Then I save the VBA code exit and go back into the Excel spreadsheet and it still says tick the past student. How can I change this?
Hello Ginny,
To change the button name in Excel and ensure it updates correctly, follow these steps:
Open Design Mode: Go to the Developer tab and click Design Mode under the Controls section.
Select the Button: Click on the button whose name you want to change.
Update the Caption: Right-click the button and choose Properties. In the Properties window, find the Caption field and update the text to the desired verbiage.
Exit Design Mode: Click Design Mode again to turn it off and test the button.
If the issue persists: Ensure there are no VBA code lines that reassign the caption during runtime (e.g., ButtonName.Caption = “Tick the passed student”). Save the workbook as a Macro-Enabled Workbook (.xlsm) and then close and reopen it to apply changes.
Regards
ExcelDemy
Hello Jay,
To ensure the checklist appends selections in any order, you can modify the code to check for and append all selected items each time the button is clicked. Here’s the updated code:
1. Added a loop to re-check all items dynamically.
2. Used a helper IsInArray function that determines if an item is already in the saved list.
3. Updates the checklist to reflect all selections dynamically.
Regards
ExcelDemy
Everything works except for the interactive button. I cannot change the name of the interactive button. I’ve gone into the VBA code and changed everywhere that says “tick the passed student” and changed it to the verbiage I needed the button to say. Then I save it and exit out of the Excel sheet. However when I go back into it the button still says tick the passed student. How can I change this button to make it say what I needed to say?
Hello Ginny,
Please check out our Youtube tutorial: How to Create Drop Down Checklist in Excel-Video
It is also attached in our article.
Regards
ExcelDemy
Hi Shamina. I was able to change the name of the interactive button and now it works. Now I need to lock cells and protect my worksheet so I can make it a form. However whenever I try to lock the cells and protect the worksheet it won’t let me lock the cell with the interactive button. And when I try to run the macro after that it throws up a bug error. How can I protect my worksheet and still make the interactive button work?
Hello Ginny,
To protect your worksheet while still allowing the interactive button and macro to function, follow these steps:
Unlock the Cells Needed for Interaction:
1. Select the cells where users need to input data or interact.
2. Right-click and choose Format Cells → Go to the Protection tab.
3. Uncheck Locked and click OK.
Allow Macros to Run on a Protected Worksheet:
1. Go to the Developer tab → Click Visual Basic (VBA editor).
2. Find your macro in the Modules section.
Modify the macro to unprotect the worksheet before running and then protect it again after execution. Example:
Protect the Worksheet:
1. Go back to Excel.
2. Click Review → Protect Sheet.
3. Enter your password and ensure the option Edit objects is checked so the button works.
Explanation:
The UserInterfaceOnly:=True option allows macros to run while still protecting the worksheet from manual changes.
Ensuring Edit objects is enabled allows the interactive button to work.
Regards
ExcelDemy
I’ve watched the entire video but it doesn’t tell me how to protect the worksheet and still allow the interactive button and Code to work. How can I protect this worksheet and still have the code work?
This allows me to click on the interactive button but shows a VBA error box “Run-time error-‘2147024809 (80070057): the specified value is out of range.
If I hit debug the interactive button works but then won’t let me click on any of the check boxes.
I am also now getting a bug error on this line
buttonShape.TextFrame2.TextRange.Characters.Text = “Click Here to Select Company”
Hi I am now able to click on the interactive box but am not able to click on any of the checkboxes once I protect the worksheet. I am also getting and error to debug on this line and I don’t why or know how to fix it.
buttonShape.TextFrame2.TextRange.Characters.Text = “Click Here to Select Company”
Hello Ginny,
To resolve both the “Run-time error -2147024809 (80070057): the specified value is out of range” and the issue with clicking checkboxes after protecting the worksheet, please follow these updated steps:
1. Modify the Code to Handle Protected Worksheets Correctly
Replace the part of your code that updates the button text with this version to ensure compatibility with protected worksheets:
Explanation:
UserInterfaceOnly:=True ensures macros can modify the sheet even when it’s protected.
DrawingObjects:=False allows interaction with form controls (including buttons and checkboxes).
2. Allow Clicking Checkboxes on a Protected Worksheet
Ensure that the protection settings allow interaction with objects and checkboxes:
2.1. Go to Review → Protect Sheet.
2.2. Enter the password.
2.3. Check Edit objects and Use PivotTable reports. This allows interaction with ActiveX controls and form elements like checkboxes.
3. Additional Tips to Avoid VBA Debug Errors
Verify that the button name in this line is correct:
Set buttonShape = ws.Shapes(“YourButtonName”)
To check the button name:
1. Click Developer → Design Mode.
2. Select the button → Right-click → Choose Properties.
3. Look at the Name field and use that exact name in the code.
Best,
ExcelDemy
I have used your code, on double clicking the button the size of the List box is increasing randomly.
Hello Aviral,
You could try checking if there is any event tied to the double-click action that could be modifying the size of the ListBox. It might be helpful to set the ListBox’s Height or Width properties explicitly and prevent them from being changed on double-click. Also, make sure there isn’t any code altering the size dynamically. If you’re using VBA, you can try adding code to handle the size more consistently, like this:
ListBox1.Width = 200
ListBox1.Height = 100
This should fix the issue by preventing the ListBox from resizing when the button is double-clicked. Let me know if this helps!
Regards
ExcelDemy