Excelovi spustni seznami so fantastično orodje za zagotavljanje doslednosti podatkov in enostavnosti vnosa. Vendar vas privzeto omejujejo na izbiro samo enega elementa. Kaj pa, če morate izbrati več elementov z istega spustnega seznama? Ta obsežen vodnik bo raziskal metode za omogočanje več izbir na Excelovih spustnih seznamih, upravljanje dvojnikov, nastavitev ločil po meri in definiranje obsega teh seznamov.

• Uporaba kode VBA
• Uporaba Kutools za Excel v nekaj klikih

• Dovolite podvojene elemente
• Odstranjevanje vseh obstoječih elementov
• Nastavitev ločila po meri
• Nastavitev določenega obsega
• Izvajanje v zaščitenem delovnem listu

Omogočanje več izbir na spustnem seznamu

V tem razdelku sta na voljo dve metodi, ki vam pomagata omogočiti več izbir na spustnem seznamu v Excelu.

Uporaba kode VBA

Če želite omogočiti več izbir na spustnem seznamu, lahko uporabite Visual Basic za aplikacije (VBA) v Excelu. Skript lahko spremeni vedenje spustnega seznama, da postane seznam z več možnostmi. Prosimo, naredite naslednje.

1. korak: Odprite urejevalnik listov (kode).

1. Odprite delovni list, ki vsebuje spustni seznam, za katerega želite omogočiti večkratno izbiro.
2. Desni klik na zavihek lista in izberite Ogled kode iz kontekstnega menija.
   

2. korak: Uporabite kodo VBA

Zdaj kopirajte naslednjo kodo VBA in jo prilepite v okno začetnega lista (koda).

Koda VBA: omogočite več izbir na spustnem seznamu 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

Rezultat

Ko se vrnete na delovni list, vam bo spustni seznam omogočil izbiro več možnosti, glejte spodnjo predstavitev:

Uporaba Kutools za Excel v nekaj klikih

Če niste zadovoljni z VBA, je lažja alternativa Kutools za Excel's Večkrat izberite spustni seznam funkcija. To uporabniku prijazno orodje poenostavi omogočanje več izbir na spustnih seznamih, kar vam omogoča, da prilagodite ločilo in preprosto upravljate dvojnike, da zadostite različnim potrebam.

po namestitev Kutools for Excel, pojdite na Kutools jeziček, izberite Spustni seznam > Večkrat izberite spustni seznam. Nato morate konfigurirati na naslednji način.

1. Določite obseg, ki vsebuje spustni seznam, s katerega morate izbrati več elementov.
2. Določite ločilo za izbrane elemente v celici spustnega seznama.
3. klik OK za dokončanje nastavitev.
   

Rezultat

Zdaj, ko kliknete na celico s spustnim seznamom v določenem obsegu, se bo poleg nje prikazalo polje s seznamom. Preprosto kliknite gumb »+« poleg elementov, da jih dodate v spustno celico, in kliknite gumb »-«, da odstranite vse elemente, ki jih ne želite več. Oglejte si spodnjo predstavitev:

Več operacij za spustni seznam z več izbirami

V tem razdelku so zbrani različni scenariji, ki so lahko potrebni pri omogočanju več izbir na spustnem seznamu Preverjanje podatkov.

Dovolite podvojene elemente na spustnem seznamu

Dvojniki so lahko težava, če je na spustnem seznamu dovoljenih več izbir. Zgornja koda VBA ne dovoljuje podvojenih elementov na spustnem seznamu. Če morate obdržati podvojene elemente, poskusite s kodo VBA v tem razdelku.

Koda VBA: Dovoli dvojnike na spustnem seznamu za preverjanje podatkov

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

Rezultat

Zdaj lahko izberete več elementov s spustnih seznamov v trenutnem delovnem listu. Če želite ponoviti element v celici spustnega seznama, nadaljujte z izbiro tega elementa s seznama. Oglejte si posnetek zaslona:

Odstranjevanje vseh obstoječih elementov s spustnega seznama

Ko izberete več elementov s spustnega seznama, boste morda morali včasih odstraniti obstoječi element iz celice spustnega seznama. V tem razdelku je še en del kode VBA, ki vam bo v pomoč pri izpolnjevanju te naloge.

Koda VBA: odstranite vse obstoječe elemente iz celice spustnega seznama

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

Rezultat

Ta koda VBA vam omogoča, da izberete več elementov s spustnega seznama in preprosto odstranite kateri koli element, ki ste ga že izbrali. Ko izberete več elementov, če želite odstraniti določenega, ga preprosto znova izberite s seznama.

Nastavitev ločila po meri

Ločilo je v zgornjih kodah VBA nastavljeno kot vejica. To spremenljivko lahko spremenite v kateri koli želeni znak, ki ga uporabite kot ločilo za izbire spustnega seznama. Tukaj je, kako lahko storite:

Kot lahko vidite, imajo zgornje kode VBA naslednjo vrstico:

delimiter = ", "

Samo spremeniti morate vejico v poljubno ločilo, kot ga potrebujete. Na primer, če želite elemente ločiti s podpičjem, spremenite vrstico v:

delimiter = "; "

delimiter = vbNewLine

Nastavitev določenega obsega

Zgornje kode VBA veljajo za vse spustne sezname v trenutnem delovnem listu. Če želite, da se kode VBA uporabljajo samo za določen obseg spustnih seznamov, lahko obseg določite v zgornji kodi VBA, kot sledi.

Kot lahko vidite, imajo zgornje kode VBA naslednjo vrstico:

Set TargetRange = Me.UsedRange

Samo spremeniti morate vrstico v:

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

Izvajanje v zaščitenem delovnem listu

Predstavljajte si, da ste delovni list zaščitili z geslom "123" in nastavite celice spustnega seznama na "odklenjena" pred aktiviranjem zaščite, s čimer zagotovite, da funkcija večkratne izbire ostane aktivna tudi po zaščiti. Vendar zgoraj omenjene kode VBA v tem primeru ne morejo delovati in ta razdelek opisuje drug skript VBA, ki je posebej zasnovan za upravljanje funkcije več izbir v zaščitenem delovnem listu.

Koda VBA: Omogoči večkratno izbiro na spustnem seznamu brez dvojnikov

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

Če omogočite več izbir v Excelovih spustnih seznamih, lahko močno izboljšate funkcionalnost in prilagodljivost svojih delovnih listov. Ne glede na to, ali ste zadovoljni s kodiranjem VBA ali imate raje enostavnejšo rešitev, kot je Kutools, imate zdaj možnost, da spremenite svoje standardne spustne sezname v dinamična orodja z več izbirami. S temi veščinami ste zdaj opremljeni za ustvarjanje bolj dinamičnih in uporabniku prijaznih Excelovih dokumentov. Za tiste, ki se želijo poglobiti v zmogljivosti Excela, se naše spletno mesto ponaša s številnimi vadnicami. Tukaj odkrijte več nasvetov in trikov za Excel.

Povezani članki