How to Create a Drop Down Checklist in Excel (with Quick Steps)

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.

Sample Dataset to Create a Drop Down Checklist in Excel

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.

Insert a List Box to Create a Drop Down Checklist in Excel

  • 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.

Access the Properties Window

  • 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.

Properties Window to Create a Drop Down Checklist in Excel

  • 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.

Create a Name for Output Cell

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.

Insert a Rectangle to Create a Button

  • 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.

Choose the Assign Macro Option

  • The Assign Macro window will appear.
  • Name your macro as Button_Click on the Macro Name: option and click on the New button.

Assign Macro Window

  • The VB Editor will open, creating a new module named Module1.

VB Editor Window

  • 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 M
    End If
    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)
        Range("CheckListOutput") = ""
    End If
End If
End Sub

VBA Code to Create a Drop Down Checklist in Excel

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.

Microsoft Excel Window

  • The Save As dialogue box will appear.
  • Choose the Save as type: option as .xlsm file and click on the Save button.

Save As Dialogue Box

  • 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.

Created an Interactive Drop Down Checklist in Excel

  • You will find your result in cell F5 and the button will be named Click Here now interactively.

Drop Down Checklist Result

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

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF