How to set multiple selections in Excel drop-down box

How to set multiple selections in excel drop-down box

1. Display the "Development Tools" tab. Open Excel 2010 and click the "File" icon in the upper left corner. Click "Options" to bring up the "Excel Options" dialog box. Check "Development Tools" in "Customize Ribbon" and click OK.

Recommended: "excel tutorial"

## 2. Set the option content of the drop-down list . Change the name of worksheet sheet2 to "data" and enter the drop-down options in column A.

3. Insert the ListBox control.

1) In worksheet sheet1, select column A, click the "Development Tools" tab, select "List Box" in "Insert", and place it in column A.

2) Select the list box control, click "Design Mode", click "Properties" in design mode, the ListBox property box will pop up, and select "Order by Category". Modify the MultiSelect item to "1 - fmMultiSelectMulti", modify the ListStyle item to "1 - fmListStyleOption", and set the ListFillRange item to the table name and cell range where the selected menu option is located. The specific format is "data!A1:A8".

4. Insert the code. In Excel, click "Visual Basic" in the development tools to open the VB editor. Double-click Sheet1 in the VB editor to open the editor of sheet1. Paste the following code into the editor and save it.

Save the code in sheet1:

Private Sub ListBox1_Change()
    If ReLoad Then Exit Sub '见下方说明
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then t = t & "," & ListBox1.List(i)
    Next
    ActiveCell = Mid(t, 2)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ListBox1
        If ActiveCell.Column = 1 And ActiveCell.Row > 1 Then
            t = 
ActiveCell.Value
            ReLoad = True '如果是根据单元格的值修改列表框，则暂时屏蔽listbox的change事件。
            For i = 0 To .ListCount - 1 '根据活动单元格内容修改列表框中被选中的内容
                If InStr(t, .List(i)) Then
                    .Selected(i) = True
                    Else
                    .Selected(i) = False
                End If
            Next
            ReLoad = False
            .Top = ActiveCell.Top + ActiveCell.Height '以下语句根据活动单元格位置显示列表框
            .Left = ActiveCell.Left
            .Width = ActiveCell.Width
            .Visible = True
            Else
            .Visible = False
        End If
    End With
End Sub

Double-click Sheet2 in the VB editor, paste the following code into the editor and save it. (This code is added to dynamically set the option value of the drop-down menu. If the option value of the drop-down menu is fixed, it can be specified directly in the ListFillRange property of the ListBox without the following code)

In sheet2 (data) Save the code in:

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("Sheet1").ListBox1.ListFillRange = "data!a1:a" & Cells(1, 1).End(xlDown).Row
End Sub

After saving the code, close the VB editor, and in the sheet1 worksheet, click on the cell in column A. The result is as shown in the figure below.

Save in module 1:

Public ReLoad As Boolean 'Switch listbox change event

The above is the detailed content of How to set multiple selections in Excel drop-down box. For more information, please follow other related articles on the PHP Chinese website!