Skip to main content

Conas liosta anuas a chruthú le ticbhoscaí iolracha in Excel?

Is iondúil go gcruthóidh a lán úsáideoirí Excel liosta anuas le ilbhoscaí seiceála d’fhonn míreanna éagsúla a roghnú ón liosta in aghaidh na huaire. I ndáiríre, ní féidir leat liosta a chruthú le ilbhoscaí seiceála le Bailíochtú Sonraí. Sa rang teagaisc seo, táimid chun dhá mhodh a thaispeáint duit chun liosta anuas a chruthú le ticbhoscaí iolracha in Excel.

Úsáid Bosca Liosta chun liosta anuas a chruthú le ilbhoscaí seiceála
A: Cruthaigh bosca liosta le sonraí foinse
B: Ainmnigh an chill ina bhfaighidh tú na míreanna roghnaithe
C: Cuir cruth isteach chun cabhrú leis na míreanna roghnaithe a aschur
Cruthaigh liosta anuas go héasca le ticbhoscaí le huirlis iontach
Tuilleadh ranganna teagaisc don liosta anuas ...

Úsáid Bosca Liosta chun liosta anuas a chruthú le ilbhoscaí seiceála

Mar atá thíos an pictiúr a thaispeántar, sa bhileog oibre reatha, beidh gach ainm i raon A2: A11 mar bhunfhoinsí sa bhosca liosta. Is féidir le cliceáil ar an gcnaipe i gcill C4 na míreanna roghnaithe a aschur, agus taispeánfar gach earra roghnaithe sa bhosca liosta i gcill E4. Chun é seo a bhaint amach, déan mar a leanas le do thoil.

A. Cruthaigh bosca liosta le sonraí foinse

1. cliceáil Forbróir > Ionsáigh > Bosca Liosta (Rialú Gníomhach X). Féach an pictiúr:

2. Tarraing bosca liosta sa bhileog oibre reatha, cliceáil ar dheis air agus ansin roghnaigh Airíonna ón roghchlár cliceáil ar dheis.

3. Sa Airíonna bosca dialóige, ní mór duit a chumrú mar seo a leanas.

  • 3.1 Sa LiostaFillRange bosca, cuir isteach an raon foinse a thaispeánfaidh tú ar an liosta (cuirim isteach an raon anseo A2: A11);
  • 3.2 Sa LiostaStyle bosca, roghnaigh StílOption 1 - fmList;
  • 3.3 Sa Il-Roghnú bosca, roghnaigh 1 - fmMultiSelectMulti;
  • 3.4 Dún an Airíonna bosca dialóige. Féach an pictiúr:

B: Ainmnigh an chill ina bhfaighidh tú na míreanna roghnaithe

Más gá duit gach earra roghnaithe a aschur i gcill shonraithe mar E4, déan mar a leanas le do thoil.

1. Roghnaigh an chill E4, iontráil ListBoxAschur isteach sa Ainm Bosca agus brúigh an Iontráil eochair.

C. Cuir cruth isteach chun cabhrú leis na míreanna roghnaithe a aschur

1. cliceáil Ionsáigh > cruthanna > Dronuilleog. Féach an pictiúr:

2. Tarraing dronuilleog i do bhileog oibre (tarraingím an dronuilleog anseo i gcill C4). Ansin cliceáil ar dheis ar an dronuilleog agus roghnaigh Macra a shannadh ón roghchlár cliceáil ar dheis.

3. Sa Macra a shannadh bosca dialóige, cliceáil an Nua cnaipe.

4. San oscailt Microsoft Visual Basic d’Fheidhmchláir fuinneog, cuir an bunchód sa Modúil fuinneog leis an gcód VBA thíos.

Cód VBA: Cruthaigh liosta le ilbhoscaí

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

Nóta: Sa chód, Dronuilleog1 an t-ainm cruth; LiostaBox1 is ainm don bhosca liosta; Roghnaigh Roghanna agus Roghanna Pickup an bhfuil na téacsanna ar taispeáint den chruth; agus an ListBoxAschur is ainm raon na cille aschuir. Is féidir leat iad a athrú bunaithe ar do chuid riachtanas.

5. Brúigh Eile + Q eochracha ag an am céanna chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

6. Déanfaidh cliceáil ar an gcnaipe dronuilleog an bosca liosta a fhilleadh nó a leathnú. Nuair a bhíonn an bosca liosta ag leathnú, seiceáil na míreanna sa bhosca liosta, agus ansin cliceáil an dronuilleog arís chun gach earra roghnaithe a aschur i gcill E4. Féach thíos an taispeántas:

7. Agus ansin sábháil an leabhar oibre mar Leabhar Oibre Excel MacroEnable chun an cód a athúsáid sa todhchaí.

Cruthaigh liosta anuas le boscaí seiceála le huirlis iontach

Tá an modh thuas ró-ildánach le láimhseáil go héasca. Seo a mholadh go mór an Liosta anuas le Boscaí Seiceála fóntais de Kutools le haghaidh Excel chun cabhrú leat liosta anuas a chruthú go héasca le boscaí seiceála i raon sonraithe, bileog oibre reatha, leabhar oibre reatha nó gach leabhar oibre oscailte bunaithe ar do chuid riachtanas. Féach an taispeántas thíos:
Íoslódáil agus bain triail as anois! (Réalta saor in aisce 30-lá)

Chomh maith leis an taispeántas thuas, soláthraímid treoir céim ar chéim freisin chun a thaispeáint conas an ghné seo a chur i bhfeidhm chun an tasc seo a bhaint amach. Déan mar a leanas le do thoil.

1. Oscail an bhileog oibre a bhfuil liosta anuas bailíochtaithe sonraí socraithe agat, cliceáil Kutools > Liosta anuas > Liosta anuas le Boscaí Seiceála > Socruithe. Féach an pictiúr:

2. Sa Socraigh Boscaí Seiceála le liosta anuas bosca dialóige, cumraigh mar a leanas le do thoil.

  • 2.1) Sa Cuir iarratas isteach roinn, sonraigh an raon feidhme feidhmeach ina gcruthóidh tú boscaí seiceála le haghaidh earraí ar an liosta anuas. Is féidir leat a raon áirithe, bileog oibre reatha, leabhar oibre reatha or gach leabhar oibre oscailte bunaithe ar do chuid riachtanas.
  • 2.2) Sa Mód roinn, roghnaigh stíl ar mhaith leat na míreanna roghnaithe a aschur;
  • Seo a thógann an Athraigh rogha mar shampla, má roghnaíonn tú é seo, athrófar luach na cille bunaithe ar na míreanna roghnaithe.
  • 2.3) Sa deighilteoir bosca, iontráil teorantóir a úsáidfidh tú chun na míreanna iolracha a scaradh;
  • 2.4) Sa Treo Téacs roinn, roghnaigh treo téacs bunaithe ar do chuid riachtanas;
  • 2.5) Cliceáil ar an OK cnaipe.

3. An chéim dheireanach, cliceáil Kutools > Liosta anuas > Liosta anuas le Boscaí Seiceála > Cumasaigh Liosta Boscaí Seiceála anuas chun an ghné seo a ghníomhachtú.

As seo amach, nuair a chliceálann tú na cealla le liosta anuas i raon feidhme sonraithe, popfaidh bosca liosta suas, roghnaigh míreanna le do thoil trí na ticbhoscaí a sheiceáil le haschur isteach i gcill mar a thaispeántar an taispeántas thíos (Tóg an modh Athraigh mar shampla ).

Le haghaidh tuilleadh sonraí faoin ngné seo, tabhair cuairt anseo.

  Más mian leat triail saor in aisce (30 lá) a bheith agat ar an bhfóntas seo, cliceáil le do thoil chun é a íoslódáil, agus ansin téigh chun an oibríocht a chur i bhfeidhm de réir na gcéimeanna thuas.

Earraí gaolmhara:

Uath-chomhiomlán agus tú ag clóscríobh liosta anuas Excel
Má tá liosta anuas bailíochtaithe sonraí agat le luachanna móra, ní mór duit scrollú síos ar an liosta chun an ceann ceart a fháil, nó an focal iomlán a chlóscríobh go díreach sa bhosca liosta. Má tá modh ann chun ligean do uathoibríoch a chomhlánú agus an chéad litir á chlóscríobh ar an liosta anuas, beidh gach rud níos éasca. Soláthraíonn an teagaisc seo an modh chun an fhadhb a réiteach.

Cruthaigh liosta anuas ó leabhar oibre eile in Excel
Tá sé furasta go leor liosta anuas bailíochtaithe sonraí a chruthú i measc bileoga oibre i leabhar oibre. Ach má aimsíonn na sonraí liosta atá uait le haghaidh bailíochtú na sonraí i leabhar oibre eile, cad a dhéanfá? Sa rang teagaisc seo, foghlaimeoidh tú conas liosta titim anuas a chruthú ó leabhar oibre eile in Excel go mion.

Cruthaigh liosta anuas inchuardaithe in Excel
Maidir le liosta anuas le go leor luachanna, ní obair éasca é ceann ceart a aimsiú. Roimhe seo thugamar isteach modh chun an liosta anuas a chomhlánú go huathoibríoch nuair a chuireann tú an chéad litir isteach sa bhosca anuas. Chomh maith leis an bhfeidhm uathchomhiomlán, is féidir leat an liosta anuas a chuardach chun an éifeachtúlacht oibre a fheabhsú chun luachanna cearta a fháil ar an liosta anuas. Chun an liosta anuas a chuardach inchuardaithe, bain triail as an modh sa rang teagaisc seo.

Déan cealla eile a uathoibriú nuair a bhíonn luachanna á roghnú agat ar liosta anuas Excel
Ligean le rá gur chruthaigh tú liosta anuas bunaithe ar na luachanna i raon cille B8: B14. Nuair a roghnaíonn tú luach ar bith ar an liosta anuas, ba mhaith leat go ndéanfaí na luachanna comhfhreagracha i raon cille C8: C14 a dhaonra go huathoibríoch i gcill roghnaithe. Chun an fhadhb a réiteach, is fearr leat na modhanna sa rang teagaisc seo.

Tuilleadh ranganna teagaisc don liosta anuas ...

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
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