在 Excel 下拉列表中選擇多個項目 – 完整指南

Excel 的下拉列表是確保數據一致性和簡化輸入的絕佳工具。然而，默認情況下，它們只允許選擇一個項目。但如果您需要從同一個下拉列表中選擇多個項目呢？本綜合指南將探討如何在 Excel 下拉列表中啟用多選、管理重複項、設置自定義分隔符以及定義這些列表的範圍。

• 使用 VBA 代碼
• 只需幾次點擊即可使用 Kutools for Excel

• 允許重複項目
• 刪除任何現有項目
• 設置自定義分隔符
• 設置指定區域
• 在受保護的工作表中執行

啟用下拉列表中的多選功能

本節提供了兩種方法，幫助您在 Excel 的下拉列表中啟用多選功能。

使用 VBA 代碼

要允許在下拉列表中進行多選，您可以使用 Excel 中的“Visual Basic for Applications”（VBA）。該腳本可以修改下拉列表的行為，使其成為多選列表。請按以下步驟操作。

步驟 1：打開工作表（代碼）編輯器

1. 打開包含您希望啟用多選功能的下拉列表的工作表。
2. 右鍵單擊工作表標籤，然後從上下文菜單中選擇“查看代碼”。
   

步驟 2：使用 VBA 代碼

現在複製以下 VBA 代碼並將其粘貼到打開的工作表（代碼）窗口中。

VBA 代碼：在 Excel 下拉列表中啟用多選功能。

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

結果

當您返回工作表時，下拉列表將允許您選擇多個選項，請參閱下面的演示：

只需幾次點擊即可使用 Kutools for Excel

如果您不熟悉 VBA，一個更簡單的替代方案是“Kutools for Excel”的“使下拉列表可多次選擇”功能。這個用戶友好的工具簡化了在下拉列表中啟用多選的功能，讓您可以輕鬆自定義分隔符並管理重複項，滿足不同的需求。

安裝 Kutools for Excel 後，轉到“Kutools”選項卡，選擇“下拉列表” > “使下拉列表可多次選擇”。然後您需要按如下方式進行配置。

1. 指定包含您需要從中選擇多個項目的下拉列表的區域。
2. 指定下拉列表單元格中所選項目的分隔符。
3. 點擊“確定”完成設置。
   

結果

現在，當您點擊指定區域內具有下拉列表的單元格時，旁邊會出現一個列表框。只需點擊項目旁的“+”按鈕即可將它們添加到下拉單元格中，點擊“-”按鈕則可刪除不再需要的項目。請參閱下面的演示：

更多多選下拉列表的操作

本節收集了在數據驗證下拉列表中啟用多選時可能需要的不同場景。

允許下拉列表中的重複項目

當允許在下拉列表中進行多選時，重複項可能會成為問題。上面的 VBA 代碼不允許在下拉列表中出現重複項目。如果您需要保留重複項目，請嘗試本節中的 VBA 代碼。

VBA 代碼：允許數據驗證下拉列表中的重複項

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range

    Set TargetRange = Me.UsedRange ' Users can change target range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        Target.Value = xValue1 & delimiter & xValue2
    End If

    Application.EnableEvents = True
    On Error GoTo 0
End Sub

結果

現在，您可以從當前工作表中的下拉列表中選擇多個項目。要在下拉列表單元格中重複某個項目，繼續從列表中選擇該項目即可。請參閱截圖：

從下拉列表中刪除任何現有項目

從下拉列表中選擇多個項目後，有時您可能需要從下拉列表單元格中刪除現有項目。本節提供了另一段 VBA 代碼來幫助您完成此任務。

VBA 代碼：從下拉列表單元格中刪除任何現有項目

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRngDV As Range
    Dim TargetRange As Range
    Dim oldValue As String
    Dim newValue As String
    Dim delimiter As String
    Dim allValues As Variant
    Dim valueExists As Boolean
    Dim i As Long
    Dim cleanedValue As String

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Set your desired delimiter here

    If Target.CountLarge > 1 Then Exit Sub

    ' Check if the change is within the specific range
    If Intersect(Target, TargetRange) Is Nothing Then Exit Sub

    On Error Resume Next
    Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
    If xRngDV Is Nothing Or Target.Value = "" Then
        ' Skip if there's no data validation or if the cell is cleared
        Application.EnableEvents = True
        Exit Sub
    End If
    On Error GoTo 0

    If Not Intersect(Target, xRngDV) Is Nothing Then
        Application.EnableEvents = False
        newValue = Target.Value
        Application.Undo
        oldValue = Target.Value
        Target.Value = newValue

        ' Split the old value by delimiter and check if new value already exists
        allValues = Split(oldValue, delimiter)
        valueExists = False
        For i = LBound(allValues) To UBound(allValues)
            If Trim(allValues(i)) = newValue Then
                valueExists = True
                Exit For
            End If
        Next i

        ' Add or remove value based on its existence
        If valueExists Then
            ' Remove the value
            cleanedValue = ""
            For i = LBound(allValues) To UBound(allValues)
                If Trim(allValues(i)) <> newValue Then
                    If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
                    cleanedValue = cleanedValue & Trim(allValues(i))
                End If
            Next i
            Target.Value = cleanedValue
        Else
            ' Add the value
            If oldValue <> "" Then
                Target.Value = oldValue & delimiter & newValue
            Else
                Target.Value = newValue
            End If
        End If

        Application.EnableEvents = True
    End If
End Sub

結果

這段 VBA 代碼允許您從下拉列表中選擇多個項目，並輕鬆刪除已選擇的任何項目。選擇多個項目後，如果想刪除特定項目，只需再次從列表中選擇它即可。

設置自定義分隔符

在上述 VBA 代碼中，分隔符設置為逗號。您可以將此變量修改為任何您喜歡的字符，以用作下拉列表選擇的分隔符。以下是具體操作方法：

可以看到，上述 VBA 代碼都包含以下這一行：

delimiter = ", "

您只需根據需要將逗號更改為任何分隔符。例如，如果您希望用分號分隔項目，請將該行更改為：

delimiter = "; "

delimiter = vbNewLine

設置指定區域

上述 VBA 代碼適用於當前工作表中的所有下拉列表。如果您只想讓 VBA 代碼適用於某些範圍的下拉列表，可以在上述 VBA 代碼中按如下方式指定範圍。

可以看到，上述 VBA 代碼都包含以下這一行：

Set TargetRange = Me.UsedRange

您只需將該行更改為：

Set TargetRange = Me.Range("C2:C10")

在受保護的工作表中執行

假設您已經使用密碼“123”保護了工作表，並在啟用保護之前將下拉列表單元格設置為“未鎖定”，從而確保保護後多選功能仍然有效。然而，上述提到的 VBA 代碼在此情況下無法運行，本節介紹了另一段專門設計用於處理受保護工作表中多選功能的 VBA 腳本。

VBA 代碼：在下拉列表中啟用無重複的多選功能

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 20240118
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    Dim delimiter As String
    Dim TargetRange As Range
    Dim isProtected As Boolean
    Dim pswd As Variant

    Set TargetRange = Me.UsedRange ' Set your specific range here
    delimiter = ", " ' Users can change the delimiter here

    If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
    
    ' Check if sheet is protected
    isProtected = Me.ProtectContents
    If isProtected Then
        ' If protected, temporarily unprotect. Adjust or remove the password as needed.
        pswd = "yourPassword" ' Change or remove this as needed
        Me.Unprotect Password:=pswd
    End If

    On Error Resume Next
    Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then
        If isProtected Then Me.Protect Password:=pswd
        Exit Sub
    End If
    Application.EnableEvents = False

    xValue2 = Target.Value
    Application.Undo
    xValue1 = Target.Value
    Target.Value = xValue2
    If xValue1 <> "" And xValue2 <> "" Then
        If Not (xValue1 = xValue2 Or _
                InStr(1, xValue1, delimiter & xValue2) > 0 Or _
                InStr(1, xValue1, xValue2 & delimiter) > 0) Then
            Target.Value = xValue1 & delimiter & xValue2
        Else
            Target.Value = xValue1
        End If
    End If

    Application.EnableEvents = True
    On Error GoTo 0

    ' Re-protect the sheet if it was protected
    If isProtected Then
        Me.Protect Password:=pswd
    End If
End Sub

通過在 Excel 下拉列表中啟用多選功能，您可以大大增強工作表的功能性和靈活性。無論您是否熟悉 VBA 編程，還是更傾向於像 Kutools 這樣的直觀解決方案，您現在都可以將標準下拉列表轉變為動態的多選工具。掌握這些技能後，您現在能夠創建更加動態且用戶友好的 Excel 文檔。對於那些渴望深入探索 Excel 功能的人，我們的網站擁有豐富的教程資源。在這裡發現更多 Excel 技巧和竅門。

相關文章