Gå til hovedindhold

Hvordan oprettes rullelisten med flere afkrydsningsfelter i Excel?

Mange Excel-brugere har tendens til at oprette rulleliste med flere afkrydsningsfelter for at vælge flere emner fra listen pr. Gang. Faktisk kan du ikke oprette en liste med flere afkrydsningsfelter med datavalidering. I denne vejledning skal vi vise dig to metoder til at oprette rulleliste med flere afkrydsningsfelter i Excel.

Brug listefelt til at oprette en rulleliste med flere afkrydsningsfelter
A: Opret en listeboks med kildedata
B: Navngiv den celle, hvor du vil finde de valgte emner
C: Indsæt en figur for at hjælpe med at output de valgte emner
Opret let rullelisten med afkrydsningsfelter med et fantastisk værktøj
Flere vejledninger til rullelisten ...

Brug listefelt til at oprette en rulleliste med flere afkrydsningsfelter

Som vist nedenfor i skærmbilledet, i det aktuelle regneark, vil alle navne i området A2: A11 være kildedataene i listeboksen. Klik på knappen i celle C4 kan output de valgte emner, og alle valgte emner i listeboksen vises i celle E4. For at opnå dette skal du gøre som følger.

A. Opret en listeboks med kildedata

1. klik Udvikler > indsatte > Listeboks (Aktiv X-kontrol). Se skærmbillede:

2. Tegn et listefelt i det aktuelle regneark, højreklik på det, og vælg derefter Ejendomme fra højreklikmenuen.

3. i Ejendomme dialogboks, skal du konfigurere som følger.

  • 3.1 I ListFillRange i feltet, skal du indtaste det kildeområde, du vil vise på listen (her indtaster jeg område A2: A11);
  • 3.2 I Listestil boks, vælg 1 - fmList StyleOption;
  • 3.3 I MultiSelect boks, vælg 1 - fmMultiSelectMulti;
  • 3.4 Luk Ejendomme dialog boks. Se skærmbillede:

B: Navngiv den celle, hvor du vil finde de valgte emner

Hvis du har brug for at outputte alle valgte emner til en bestemt celle, såsom E4, skal du gøre som følger.

1. Vælg celle E4, indtast ListBoxOutput ind i Navn Box og tryk på Indtast nøgle.

C. Indsæt en figur for at hjælpe med at udføre de valgte emner

1. klik indsatte > Former > Rektangel. Se skærmbillede:

2. Tegn et rektangel i dit regneark (her tegner jeg rektanglet i celle C4). Højreklik derefter på rektanglet, og vælg Tildel makro fra højreklikmenuen.

3. i Tildel makro dialogboksen, skal du klikke på knappen Ny .

4. I åbningen Microsoft Visual Basic til applikationer vindue, skal du udskifte den originale kode i Moduler vindue med nedenstående VBA-kode.

VBA-kode: Opret en liste med flere afkrydsningsfelter

Sub Rectangle1_Click()
'Updated by Extendoffice 20200730
Dim xSelShp As Shape, xSelLst As Variant, I, J As Integer
Dim xV As String
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox1
If xLstBox.Visible = False Then
    xLstBox.Visible = True
    xSelShp.TextFrame2.TextRange.Characters.Text = "Pickup Options"
    xStr = ""
    xStr = Range("ListBoxOutput").Value
    
    If xStr <> "" Then
         xArr = Split(xStr, ";")
    For I = xLstBox.ListCount - 1 To 0 Step -1
        xV = xLstBox.List(I)
        For J = 0 To UBound(xArr)
            If xArr(J) = xV Then
              xLstBox.Selected(I) = True
              Exit For
            End If
        Next
    Next I
    End If
Else
    xLstBox.Visible = False
    xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"
    For I = xLstBox.ListCount - 1 To 0 Step -1
        If xLstBox.Selected(I) = True Then
        xSelLst = xLstBox.List(I) & ";" & xSelLst
        End If
    Next I
    If xSelLst <> "" Then
        Range("ListBoxOutput") = Mid(xSelLst, 1, Len(xSelLst) - 1)
    Else
        Range("ListBoxOutput") = ""
    End If
End If
End Sub

Bemærk: I koden, Rektangel1 er formnavnet ListBox1 er navnet på listeboksen; Vælg Valg , Afhentningsindstillinger er de viste tekster af formen; og ListBoxOutput er outputnavnet for outputcellen. Du kan ændre dem ud fra dine behov.

5. Trykke andre + Q taster samtidigt for at lukke Microsoft Visual Basic til applikationer vindue.

6. Klik på rektangelknappen for at folde eller udvide listeboksen. Når listeboksen udvides, skal du markere elementerne i listeboksen og derefter klikke på rektanglet igen for at outputte alle valgte emner i celle E4. Se nedenstående demo:

7. Og gem derefter projektmappen som en Excel MacroEnable-projektmappe for at genbruge koden i fremtiden.

Opret rulleliste med afkrydsningsfelter med et fantastisk værktøj

Ovenstående metode er for flertrins til at håndtere let. Her kan varmt anbefales Rulleliste med afkrydsningsfelter nytte af Kutools til Excel for at hjælpe dig med nemt at oprette rullelisten med afkrydsningsfelter i et bestemt område, det aktuelle regneark, den aktuelle projektmappe eller alle åbnede projektmapper baseret på dine behov. Se nedenstående demo:
Download og prøv det nu! (30-dages gratis spor)

Udover ovenstående demo giver vi også en trinvis vejledning til at demonstrere, hvordan du anvender denne funktion til at nå denne opgave. Gør som følger.

1. Åbn det regneark, du har indstillet rullelisten til datavalidering, klik på Kutools > Rulleliste > Rulleliste med afkrydsningsfelter > Indstillinger. Se skærmbillede:

2. i Drop-down List med afkrydsningsfelter Indstillinger dialogboksen, skal du konfigurere som følger.

  • 2.1) I Anvend på sektion, angiv det anvendelsesområde, hvor du opretter afkrydsningsfelter for emner i rullelisten. Du kan angive en bestemt rækkevidde, det aktuelle regneark, nuværende projektmappe or alle åbnede projektmapper baseret på dine behov.
  • 2.2) I tilstand sektion, vælg en stil, som du vil udsende de valgte emner;
  • Her tager den Ændre indstilling som et eksempel. Hvis du vælger dette, ændres celleværdien baseret på de valgte emner.
  • 2.3) I Separator boks, skal du indtaste en afgrænser, som du vil bruge til at adskille de flere emner;
  • 2.4) I Tekstretning sektion, vælg en tekstretning baseret på dine behov;
  • 2.5) Klik på OK .

3. Klik på det sidste trin Kutools > Rulleliste > Rulleliste med afkrydsningsfelter > Aktivér rulleliste med afkrydsningsfelter for at aktivere denne funktion.

Fra nu af, når du klikker på cellerne med rullelisten i et specificeret omfang, vises der en listeboks, vælg venligst emner ved at markere afkrydsningsfelterne, der skal udføres i celle som nedenstående demo vist (Tag Modify-tilstanden som et eksempel ).

For flere detaljer om denne funktion, besøg her.

  Hvis du vil have en gratis prøveperiode (30 dage) af dette værktøj, klik for at downloade det, og gå derefter til at anvende handlingen i henhold til ovenstående trin.

Relaterede artikler:

Autofuldfør, når du skriver i rullelisten i Excel
Hvis du har en datavalideringsrulleliste med store værdier, skal du rulle ned på listen bare for at finde den rigtige eller skrive hele ordet direkte i listeboksen. Hvis der er en metode til at tillade automatisk udfyldning, når du skriver det første bogstav i rullelisten, bliver alt lettere. Denne vejledning giver metoden til at løse problemet.

Opret rullelisten fra en anden projektmappe i Excel
Det er ret nemt at oprette en rulleliste med datavalidering blandt regneark i en projektmappe. Men hvis de listedata, du har brug for til datavalidering, findes i en anden projektmappe, hvad ville du gøre? I denne vejledning lærer du, hvordan du opretter en drop fown-liste fra en anden projektmappe i Excel i detaljer.

Opret en søgbar rulleliste i Excel
For en rulleliste med mange værdier er det ikke let at finde en ordentlig. Tidligere har vi introduceret en metode til automatisk udfyldelse af rullelisten, når du indtaster det første bogstav i rullemenuen. Udover funktionen autofuldførelse kan du også gøre rullelisten søgbar for at forbedre arbejdseffektiviteten ved at finde korrekte værdier i rullelisten. For at gøre rullelisten søgbar, prøv metoden i denne vejledning.

Udfyld automatisk andre celler, når du vælger værdier i rullelisten i Excel
Lad os sige, at du har oprettet en rulleliste baseret på værdierne i celleområde B8: B14. Når du vælger en hvilken som helst værdi i rullelisten, ønsker du, at de tilsvarende værdier i celleområde C8: C14 automatisk udfyldes i en valgt celle. Til løsning af problemet vil metoderne i denne vejledning gøre dig en tjeneste.

Flere vejledninger til rullelisten ...

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier  |   Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar   |  Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler   |  Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt:  12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
Comments (70)
No ratings yet. Be the first to rate!
MS
MS
This comment was minimized by the moderator on the site
Hello-

This is fabulous, but I was wondering if there is a way to call the code as a subroutine, ie Click Button 1, run this code with X List Box and X Output cell. I want to pass the listbox and the output cell as variables into this code. Any help would be greatly appreciated.

I've tried this:
Private Sub Rectangle1_Click()
Call MultiSelctDropdown(ListBox1,Output1)
End Sub

Private Sub Rectangle2_Click()
Call MultiSelctDropdown(ListBox2,Output2)
End Sub

Private Sub MultiSelectDropdown(ListBox As String, Output As String)
Dim xSelShp As Shape, xSelLst As Variant, I, J As Integer
Dim xV As String
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox
If xLstBox.Visible = False Then
xLstBox.Visible = True
xSelShp.TextFrame2.TextRange.Characters.Text = "Enter"
xStr = ""
xStr = Range("Output").Value

If xStr <> "" Then
xArr = Split(xStr, ",")
For I = xLstBox.ListCount - 1 To 0 Step -1
xV = xLstBox.List(I)
For J = 0 To UBound(xArr)
If xArr(J) = xV Then
xLstBox.Selected(I) = True
Exit For
End If
Next
Next I
End If
Else
xLstBox.Visible = False
xSelShp.TextFrame2.TextRange.Characters.Text = "Click Here to Select Products"
For I = xLstBox.ListCount - 1 To 0 Step -1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I) & "," & xSelLst
End If
Next I
If xSelLst <> "" Then
Range("Output") = Mid(xSelLst, 1, Len(xSelLst) - 1)
Else
Range("Output") = ""
End If
End If
End Sub
Reply
0
0
MS
MS     MS
This comment was minimized by the moderator on the site
Ok I figured this one out (see below)

But now I want to have only ONE list box that I can use over and over again with different buttons but different output depending on the button pushed. And the code below works for this EXCEPT the items selected when the list box pops up includes all items that have been outputted from the code.

If list box1 contains

Apples
Oranges
Pears
Kiwi

and button 1 is pressed and Apples is selected, when button 2 is pressed Apples is already selected, and if during button press 2 pears is selected when you go back to button 1 Apples AND Pears are selected.

How can I either clear all selected when a button is pressed OR make the selected options equal to the output.


Private Sub Button1_Click()
Call ProductSelection(ActiveSheet.ListBox1, "Button1Output", 243, 215)
End Sub
Private Sub Button2_Click()
Call ProductSelection(ActiveSheet.ListBox1, "Button2Output", 472, 215)
End Sub



Private Sub ProductSelection(xListBox As Object, Output As String, left As Integer, height As Integer)
Dim xSelShp As Shape, xSelLst As Variant, I, J As Integer
Dim xV As String
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = xListBox
If xLstBox.Visible = False Then
xLstBox.Visible = True
xLstBox.left = left
xLstBox.height = height
xSelShp.TextFrame2.TextRange.Characters.Text = "Enter"
xStr = ""
xStr = Range(Output).Value

If xStr <> "" Then
xArr = Split(xStr, ",")
For I = xLstBox.ListCount - 1 To 0 Step -1
xV = xLstBox.List(I)
For J = 0 To UBound(xArr)
If xArr(J) = xV Then
xLstBox.Selected(I) = True
Exit For
End If
Next
Next I
End If
Else
xLstBox.Visible = False
xSelShp.TextFrame2.TextRange.Characters.Text = "Click Here to Select Products"
For I = xLstBox.ListCount - 1 To 0 Step -1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I) & "," & xSelLst
End If
Next I
If xSelLst <> "" Then
Range(Output) = Mid(xSelLst, 1, Len(xSelLst) - 1)
Else
Range(Output) = ""
End If
End If
End Sub
Reply
0
0
Meghan
Meghan
This comment was minimized by the moderator on the site
Hi there- this is super helpful, thank you! Can you tell me how I can draw a list box based on a list in a different worksheet (but same file)? I've tried entering my worksheet name (i.e., 'lists') followed by the range in the list fill range (after clicking on Properties) but this does not work.Thanks!
Reply
0
0
crystal
crystal     Meghan
This comment was minimized by the moderator on the site
Hi Meghan,Supposing you want to <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit;">ListBox1</span><span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit;">Sheet1</span><div data-tag="code">Sub listboxlistfillrangefromdifferentsheet()
Sheet1.ListBox1.ListFillRange = Sheet2.Range("A2:A20").Address(, , , True)
End Sub
Reply
Report
0
0
mmp13
mmp13
This comment was minimized by the moderator on the site
hello, I have a problem with the list box: to make the list going down, I have to click on the box that allows the list to go down but when I click, it does not go down automatically, I have to click outside the list so that it refreshes and the list goes down, what to do? Thank you
Reply
Report
0
0
crystal
crystal     mmp13
This comment was minimized by the moderator on the site
Hi,You can't scroll ActiveX Listbox by mouse wheel. There is no setting for it.

Reply
Report
0
0
faez
faez
This comment was minimized by the moderator on the site
Hi, thank you for sharing this! I have a question though, is it possible to populate different cells based on the selected option?For example, instead of having everything in one cell, each selection is populated in the cell below the earlier selection. Thank you!
Reply
0
0
crystal
crystal     faez
This comment was minimized by the moderator on the site
Hi faez,
The VBA below helps to populate the selected options in different cells on the same row. Please have a try.

Sub Rectangle2_Click()
'Updated by Extendoffice 20211124
Dim xSelShp As Shape, xSelLst As Variant, I As Integer
Dim xRg As Range
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox1
If xLstBox.Visible = False Then
xLstBox.Visible = True
xSelShp.TextFrame2.TextRange.Characters.Text = "Pickup Options"
Else
xLstBox.Visible = False
xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"
Set xRg = Range("ListBoxOutput")
For I = 0 To xLstBox.ListCount - 1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I)
xRg.Value = Mid(xSelLst, 1, Len(xSelLst))
Set xRg = xRg.Offset(0, 1)
End If
Next I
End If
End Sub
Reply
Report
0
0
Eloi
Eloi
This comment was minimized by the moderator on the site
Hi Crystal,
Thanks a lot for this code, very helpful and convenient. One question : how to adpat it in order not to have the separator ";" if only one item is selected ?
Reply
0
0
crystal
crystal     Eloi
This comment was minimized by the moderator on the site
Hi Eloi,No separator is displayed when you select only one item in the list.
Reply
Report
0
0
eloi
eloi     crystal
This comment was minimized by the moderator on the site
Thanks Crystal, the mistake was in my adaptation of the code.
If someone needs to adapt it with a click on a cell instead of a click on a shape, you could try this (with a call to this sub in your sheet, with a condition when your cell is selected)

Sub affichage_liste(xLstBox As MSForms.ListBox, texte1 As String)
'Updated by Extendoffice 20200730
Dim xSelLst As Variant, I, J As Integer
Dim xV As String

If xLstBox.Visible = False Then
xLstBox.Visible = True
xStr = ""
xStr = Range(texte1).Value

If xStr <> "" Then
xArr = Split(xStr, ";")
For I = xLstBox.ListCount - 1 To 0 Step -1
xV = xLstBox.List(I)
For J = 0 To UBound(xArr)
If xArr(J) = xV Then
xLstBox.Selected(I) = True
Exit For
End If
Next
Next I
End If
Else
xLstBox.Visible = False
For I = xLstBox.ListCount - 1 To 0 Step -1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I) & "; " & xSelLst
End If
Next I
If xSelLst <> "" Then
Range(texte1) = Mid(xSelLst, 1, Len(xSelLst) - 2)
Else
Range(texte1) = ""
End If
End If
End Sub
Reply
0
0
crystal
crystal     eloi
This comment was minimized by the moderator on the site
Hi Eloi,The code you provided doesn't seem to work. I have modified it again as below.  After adding the code in your Sheet(Code) window, go back to the worksheet, click the cell C4 to expand the list box, after selecting items from the list box, click on any cell in the worksheet to output the selection, and no separator is displayed when you select only one item in the list.
<div data-tag="code">Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updated by Extendoffice 20211223
Dim xSelLst As Variant, I, J As Integer
Dim xV As String
Set xLstBox = ActiveSheet.ListBox1

If Target.Address = "$C$4" Then


If xLstBox.Visible = False Then
xLstBox.Visible = True
xStr = ""
xStr = Range("ListBoxOutput").Value

If xStr <> "" Then
xArr = Split(xStr, ";")
For I = xLstBox.ListCount - 1 To 0 Step -1
xV = xLstBox.List(I)
For J = 0 To UBound(xArr)
If xArr(J) = xV Then
xLstBox.Selected(I) = True
Exit For
End If
Next
Next I
End If

End If

Else
xLstBox.Visible = False

For I = xLstBox.ListCount - 1 To 0 Step -1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I) & "; " & xSelLst
End If
Next I
If xSelLst <> "" Then
Range("ListBoxOutput") = Mid(xSelLst, 1, Len(xSelLst) - 2)
Else
Range("ListBoxOutput") = ""
End If


End If

End Sub
Reply
Report
0
0
eloi
eloi     crystal
This comment was minimized by the moderator on the site
Thanks a lot Crystal
Reply
0
0
Morgane K
Morgane K
This comment was minimized by the moderator on the site
Bonjour,Je suis plus que novice sur excel étant sur mac je ne peux utiliser l'outil Kutools j'ai donc tenté de créer une liste déroulante où l'on peut cocher plusieurs items mais je bloque dès le début dans l'onglet développeur puisque je n'ai pas du tout l'outil "insert".Merci pour votre aide
Reply
0
0
Gowtham
Gowtham
This comment was minimized by the moderator on the site
Hi I am newbie to VBA. I tried to execute the code but i get the following error "Run-time error '-2147024809 (80070057)': The Item with the specified name wasn't found". Can you help me with this
Reply
0
0
crystal
crystal     Gowtham
This comment was minimized by the moderator on the site
Hi Gowtham,It seem that this error occurs when you running the code directly in the Code editor (the Microsoft Visual Basic for Applications window).After adding the code, please press the Alt + Q keys to close the Microsoft Visual Basic for Applications window. Go back to the worksheet and execute the code by clicking the rectangle button (see the .gif picture in step 6).
Reply
Report
0
0
minapnh
minapnh     crystal
This comment was minimized by the moderator on the site
Hi Crystal, even after your tip am getting same error as Gowtham. My error is right after protect my sheet. Would you please help me with this issue?
Reply
Report
0
0
Mina
Mina     crystal
This comment was minimized by the moderator on the site
Hi Crystal, Even After your tip I am getting same error as Gowtham.
Reply
0
0
crystal
crystal     Mina
This comment was minimized by the moderator on the site
Hi Mina,Which Excel and Windows version are you using?
Reply
Report
0
0
fbjr
fbjr
This comment was minimized by the moderator on the site
Hello,I added this code to an existing macro template and it is loading the selections correctly, but it is NOT clearing out the x on the selected items..This will be used on/in a template worksheet that has submit button/macro to load the worksheet answers into a hidden worksheet with a data table.And am happy to say the field data loaded to the cell, transferred into my variable, and loaded to the data table as expected.
This code was a HUGE blessing!
I use excel 2016
How do I fix this. I am using this version from below.
Sub Rectangle1_Click()
'Updated by Extendoffice 20200730
Dim xSelShp As Shape, xSelLst As Variant, I, J As Integer
Dim xV As String
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox1
If xLstBox.Visible = False Then
xLstBox.Visible = True
xSelShp.TextFrame2.TextRange.Characters.Text = "Pickup Options"
xStr = ""
xStr = Range("ListBoxOutput").Value

If xStr <> "" Then
xArr = Split(xStr, ";")
For I = xLstBox.ListCount - 1 To 0 Step -1
xV = xLstBox.List(I)
For J = 0 To UBound(xArr)
If xArr(J) = xV Then
xLstBox.Selected(I) = True
Exit For
End If
Next
Next I
End If
Else
xLstBox.Visible = False
xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"
For I = xLstBox.ListCount - 1 To 0 Step -1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I) & ";" & xSelLst
End If
Next I
If xSelLst <> "" Then
Range("ListBoxOutput") = Mid(xSelLst, 1, Len(xSelLst) - 1)
Else
Range("ListBoxOutput") = ""
End If
End If
End Sub
Reply
0
0
ben
ben
This comment was minimized by the moderator on the site
Hello,

I'm having a similar problem to Tom from 2 months ago. When I try to share my file with a colleague, the multi-select droplist list isn't working. However, I used the Kutools add-on to create this as opposed to creating it myself. I've also saved it as macro-enabled.
Reply
0
0
crystal
crystal     ben
This comment was minimized by the moderator on the site
Hi ben,The multi-select drop down list feature of Kutools only works in the Excel that installed our Kutools. We are working on this issue, sorry for the inconvenience.
Reply
Report
0
0
Tom
Tom
This comment was minimized by the moderator on the site
Hello I looking the resolve for problem with saving choosing on drop down list

when i choose something on list and send file to my colleague, then when he open file and want to check my list then list has cleared and cell "ListBoxOutput" was cleared too.

help please :)
Reply
0
0
crystal
crystal     Tom
This comment was minimized by the moderator on the site
Hi Tom,
Please save the workbook as an "Excel MacroEnable Workbook" and then send this .xlsm file to your colleague.
Reply
Report
0
0
Tom
Tom     crystal
This comment was minimized by the moderator on the site
hello i save this file in this format from beginning ;), but without effect. still when i fill file and send to someone then when he opened file and click to "shape" then macro started from begin and cleared list
Reply
0
0
crystal
crystal     Tom
This comment was minimized by the moderator on the site
Hi Tom,
I am sorry for the mistake. The code has been updated again. Please have a try.

Sub Rectangle1_Click()

'Updated by Extendoffice 20200730

Dim xSelShp As Shape, xSelLst As Variant, I, J As Integer

Dim xV As String

Set xSelShp = ActiveSheet.Shapes(Application.Caller)

Set xLstBox = ActiveSheet.ListBox1

If xLstBox.Visible = False Then

xLstBox.Visible = True

xSelShp.TextFrame2.TextRange.Characters.Text = "Pickup Options"

xStr = ""

xStr = Range("ListBoxOutput").Value



If xStr <> "" Then

xArr = Split(xStr, ";")

For I = xLstBox.ListCount - 1 To 0 Step -1

xV = xLstBox.List(I)

For J = 0 To UBound(xArr)

If xArr(J) = xV Then

xLstBox.Selected(I) = True

Exit For

End If

Next

Next I

End If

Else

xLstBox.Visible = False

xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"

For I = xLstBox.ListCount - 1 To 0 Step -1

If xLstBox.Selected(I) = True Then

xSelLst = xLstBox.List(I) & ";" & xSelLst

End If

Next I

If xSelLst <> "" Then

Range("ListBoxOutput") = Mid(xSelLst, 1, Len(xSelLst) - 1)

Else

Range("ListBoxOutput") = ""

End If

End If

End Sub
Reply
Report
0
0
Tom
Tom     crystal
This comment was minimized by the moderator on the site
Now it's working perfectly.

Many thanks for your help
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations