Note: The other languages of the website are Google-translated. Back to English
Mewngofnodi  \/ 
x
or
x
Cofrestru  \/ 
x

or

Sut i awtocomplete wrth deipio rhestr ostwng Excel?

I gael gwymplen dilysu data gyda llawer o eitemau, mae angen i chi sgrolio i fyny ac i lawr yn y rhestr i ddod o hyd i'r un sydd ei hangen arnoch neu deipio'r gair cyfan yn y blwch rhestr yn gywir. A oes unrhyw ddulliau i wneud rhestr ostwng wedi'i chwblhau'n awtomatig wrth deipio'r llythyr cyntaf neu unrhyw nodau cymharol? Bydd hynny'n helpu pobl i weithio'n fwy effeithlon mewn taflenni gwaith gyda rhestrau cwympo mewn celloedd. Mae'r tiwtorial hwn yn darparu dau ddull i'ch helpu chi i'w gyflawni.

Yn awtomataidd wrth deipio'r gwymplen gyda chod VBA
Yn awtomataidd wrth deipio'r gwymplen gydag offeryn anhygoel

Mwy o sesiynau tiwtorial ar gyfer y gwymplen ...


Yn awtomataidd wrth deipio'r gwymplen gyda chod VBA

Gwnewch fel a ganlyn i wneud rhestr ostwng yn awtocomplete ar ôl teipio llythrennau cyfatebol yn y gell.

Yn gyntaf, mae angen i chi fewnosod blwch combo yn y daflen waith a newid ei briodweddau.

1. Agorwch y daflen waith sy'n cynnwys y gwymplen rydych chi am ei gwneud yn awtocomplete.

2. Cyn mewnosod blwch Combo, mae angen ichi ychwanegu'r tab Datblygwr i'r rhuban Excel. Os yw'r tab Datblygwr yn dangos ar eich rhuban, symud i gam 3. Fel arall, gwnewch fel a ganlyn: Cliciwch ffeil > Dewisiadau i agor y Dewisiadau ffenestr. Yn hyn Dewisiadau Excel ffenestr, cliciwch Rhinwedd Customize yn y cwarel chwith, gwiriwch y Datblygwr blwch, ac yna cliciwch ar y OK botwm. Gweler y screenshot:

3. Cliciwch Datblygwr > Mewnosod > Blwch Combo (Rheoli ActiveX).

4. Tynnwch lun blwch combo yn y daflen waith gyfredol. Cliciwch ar y dde ac yna dewiswch Eiddo o'r ddewislen clicio ar y dde.

5. Yn y Eiddo blwch deialog, amnewidiwch y testun gwreiddiol yn y (Enw) cae gyda TempCombo.

6. Trowch oddi ar y Modd Dylunio trwy glicio Datblygwr > Modd Dylunio.

Yna, cymhwyswch y cod VBA isod

7. Cliciwch ar y dde ar y tab dalen gyfredol a chlicio Gweld y Cod o'r ddewislen cyd-destun. Gweler y screenshot:

8. Yn yr agoriad Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïwch a gludwch y cod VBA isod i mewn i ffenestr Cod y daflen waith.

Cod VBA: Yn awtomataidd wrth deipio'r gwymplen

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

9. Gwasgwch Alt + Q allweddi ar yr un pryd i gau'r Cymwysiadau Sylfaenol Gweledol Microsoft ffenestr.

O hyn ymlaen, pan gliciwch ar gell rhestr ostwng, bydd y gwymplen yn annog yn awtomatig. Gallwch chi ddechrau teipio'r llythyr i mewn i wneud i'r eitem gyfatebol gael ei chwblhau'n awtomatig mewn cell ddethol. Gweler y screenshot:

Nodyn: Nid yw'r cod hwn yn gweithio ar gyfer celloedd unedig.


Yn awtomataidd wrth deipio'r gwymplen gydag offeryn anhygoel

Gan fod VBA yn anodd ei drin i lawer o ddefnyddwyr Excel, dyma argymell y Rhestr Gostwng Chwiliadwy nodwedd o Kutools ar gyfer Excel i'ch helpu chi i gwblhau rhestr ostwng yn awtomatig wrth deipio'r cymeriad cyntaf.

Nodyn: Cyn defnyddio'r nodwedd, gwnewch yn siŵr eich bod wedi creu gwymplenni yn y daflen waith. Yn yr achos hwn, rhoddir y rhestrau gwympo yn D3: D9.

1. Cliciwch Kutools > Rhestr Gollwng > Rhestr Gostwng Chwiliadwy > Gosodiadau.

2. Yn y popping up Rhestr Gostwng Chwiliadwy blwch deialog, mae angen i chi wneud y gosodiadau canlynol.

2.1) Yn y Gwnewch gais i adran, nodwch ystod neu daflen waith lle rydych chi am gymhwyso'r nodwedd gwymplen chwiliadwy;
Awgrymiadau: Ar ôl dewis y Cwmpas Penodedig botwm radio, gallwch ddewis Taflen waith gyfredol, Llyfr gwaith cyfredol or Pob llyfr gwaith o'r gwymplen yn ôl eich anghenion.
2.2) Yn y Dewisiadau adran, dewiswch y Yn cyfateb i ddim ond dechrau'r gair opsiwn neu Achos sensitif opsiwn neu'r ddau;
Yn cyfateb i ddim ond dechrau'r gair: Gwiriwch yr opsiwn hwn, dim ond yr eitemau sy'n dechrau gyda'r cymeriad wedi'i deipio sy'n cael eu harddangos, a bydd yr eitem gyfatebol gyntaf yn cael ei chwblhau yn y blwch rhestr yn awtomatig. Dad-diciwch yr opsiwn hwn i arddangos eitemau sy'n cynnwys y cymeriad wedi'i deipio.
Achos sensitif: Gwiriwch yr opsiwn hwn i berfformio paru achos-sensitif gyda'r nodau teipio.
2.3) Cliciwch OK.

3. Ar ôl gorffen y gosodiadau, mae angen i chi alluogi'r nodwedd trwy glicio Kutools > Rhestr Gollwng > Rhestr Gostwng Chwiliadwy > Galluogi Rhestr Gostwng Chwiliadwy.

Nawr, pan gliciwch y gell gwymplen, mae blwch rhestr gyda'r holl eitemau yn cael ei arddangos. Nid oes ond angen i chi deipio cymeriad yn y blwch testun i arddangos yr holl eitemau cyfatebol, a defnyddio'r Up or Down saeth i ddewis yr eitem sydd ei hangen arnoch neu dim ond gadael i'r eitem baru gyntaf lenwi'r blwch rhestr yn awtomatig, ac yna pwyso'r Rhowch allwedd i awtocomplete y gell gwymplen gyda'r eitem paru. Gweler y demo isod.

Cliciwch i wybod mwy am y nodwedd hon.

  Os ydych chi am gael treial am ddim (30 diwrnod) o'r cyfleustodau hwn, cliciwch i'w lawrlwytho, ac yna ewch i gymhwyso'r llawdriniaeth yn ôl y camau uchod.


Erthyglau cysylltiedig:

Sut i greu gwymplen gyda blychau gwirio lluosog yn Excel?
Mae llawer o ddefnyddwyr Excel yn tueddu i greu gwymplen gyda blychau gwirio lluosog er mwyn dewis nifer o eitemau o'r rhestr bob tro. Mewn gwirionedd, ni allwch greu rhestr gyda blychau gwirio lluosog gyda Dilysu Data. Yn y tiwtorial hwn, rydyn ni'n mynd i ddangos dau ddull i chi greu gwymplen gyda blychau gwirio lluosog yn Excel. Mae'r tiwtorial hwn yn darparu'r dull i ddatrys y broblem.

Creu rhestr ostwng o lyfr gwaith arall yn Excel
Mae'n eithaf hawdd creu gwymplen dilysu data ymhlith taflenni gwaith mewn llyfr gwaith. Ond os yw'r data rhestr sydd ei angen arnoch ar gyfer dilysu'r data yn lleoli mewn llyfr gwaith arall, beth fyddech chi'n ei wneud? Yn y tiwtorial hwn, byddwch yn dysgu sut i greu rhestr gollwng o lyfr gwaith arall yn Excel yn fanwl.

Creu rhestr ostwng y gellir ei chwilio yn Excel
Ar gyfer rhestr ostwng sydd â nifer o werthoedd, nid yw dod o hyd i un iawn yn waith hawdd. Yn flaenorol rydym wedi cyflwyno dull o gwblhau rhestr ostwng yn awtomatig wrth nodi'r llythyr cyntaf yn y gwymplen. Heblaw am y swyddogaeth awtocomplete, gallwch hefyd wneud y rhestr ostwng yn chwiliadwy am wella'r effeithlonrwydd gweithio wrth ddod o hyd i werthoedd cywir yn y gwymplen. Ar gyfer chwilio rhestr ostwng, rhowch gynnig ar y dull yn y tiwtorial hwn.

Auto poblogi celloedd eraill wrth ddewis gwerthoedd yn rhestr ostwng Excel
Gadewch i ni ddweud eich bod wedi creu rhestr ostwng yn seiliedig ar y gwerthoedd yn ystod celloedd B8: B14. Pan ddewiswch unrhyw werth yn y gwymplen, rydych chi am i'r gwerthoedd cyfatebol yn ystod celloedd C8: C14 gael eu poblogi'n awtomatig mewn cell ddethol. Ar gyfer datrys y broblem, bydd y dulliau yn y tiwtorial hwn yn ffafrio chi.

Mwy o sesiynau tiwtorial ar gyfer y gwymplen ...


Yr Offer Cynhyrchedd Swyddfa Gorau

Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%

  • Ailddefnyddio: Mewnosod yn gyflym fformwlâu cymhleth, siartiau ac unrhyw beth rydych chi wedi'i ddefnyddio o'r blaen; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau heb golli Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi / Colofnau Dyblyg... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Mwy na 300 o nodweddion pwerus. Yn cefnogi Swyddfa / Excel 2007-2019 a 365. Yn cefnogi pob iaith. Defnydd hawdd yn eich menter neu sefydliad. Nodweddion llawn treial am ddim 30 diwrnod. Gwarant arian yn ôl 60 diwrnod.
tab kte 201905

Mae Tab Office yn Dod â rhyngwyneb Tabbed i'r Swyddfa, a Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod officetab
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mbuchmeier · 3 days ago
    This code works really well for a single drop down. However, if I have 5 dropdowns using the same list of values but they need to operate independently of each other how is that accomplished? Despite my best efforts, if I put more than one combo box on the page, any selection is one is mirrored in the other.
    • To post as a guest, your comment is unpublished.
      crystal · 23 hours ago
      Hi Mbuchmeier,
      Only need one combo box on the page. The code works on all drop-down lists in current page. You just need to manually click on the drop-down list cell to activate the combo box.
  • To post as a guest, your comment is unpublished.
    B.K · 10 days ago
    will, thank you is not enugh :::: alot of thank you then ;;;;; that worked like magic :)
  • To post as a guest, your comment is unpublished.
    Nick M · 1 months ago
    Yeah, basically completely useless.  Want the cell to auto-complete when typing in a cell using list data validation.
    Tried this, and now I have to start over from scratch because I can't Undo it.  Thanks.
    Also loaded with syntax errors.
  • To post as a guest, your comment is unpublished.
    Ahmed · 2 months ago
    It works as it should be except for two things, first, there is no validation after insertion. i.e. if I typed anything at the combo then clicked enter it will accept the typed value while it should not do this since the data validation is being used to prevent such behaviors and make sure that the entered data is from selected range. Second, at the data validation list, sometimes I use big range with empty cells and select ignore empty at validation list but the combo takes all the range and shows it, will be nice to remove the empty cells from the combo range.

    Thanks & hope you can implement these things to make it perfect. 
    • To post as a guest, your comment is unpublished.
      crystal · 29 days ago
      Hi, Ahmed
      The VBA below helps to solve the problem of blank cells. 
      Since the Combo box accepts the value that are not in the list, we still can't find a way to solve this problem. Sorry for the inconvenience.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Update by Extendoffice: 2021/11/05 Dim xCombox As OLEObject Dim xStr As String Dim xWs As Worksheet Dim xArr, xArr1 Dim xRg As Range Dim xSrc As Variant Dim xI, xJ, xIndex, xCount As Integer Set xWs = Application.ActiveSheet On Error Resume Next Set xCombox = xWs.OLEObjects("TempCombo") With xCombox .ListFillRange = "" .LinkedCell = "" .Visible = False End With If Target.Validation.Type = 3 Then Target.Validation.InCellDropdown = False Cancel = True xStr = Target.Validation.Formula1 xStr = Right(xStr, Len(xStr) - 1) If xStr = "" Then Exit Sub Set xRg = Range(xStr) If xRg Is Nothing Then xArr1 = Split(xStr, ",") Else ReDim xArr1(0 To xRg.Count - 1) For xI = 0 To xRg.Count - 1 xArr1(xI) = xRg.Item(xI + 1).Value Next End If xI = 0 xCount = UBound(xArr1) + 1 For xJ = 0 To UBound(xArr1) If Replace(xArr1(xJ), " ", "") = "" Then xCount = xCount - 1 Next ReDim xArr(0 To xCount - 1) xIndex = 0 For xJ = 0 To UBound(xArr1) If Replace(xArr1(xJ), " ", "") <> "" Then xArr(xIndex) = xArr1(xJ) xIndex = xIndex + 1 End If Next xStr = "" With xCombox .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .Height = Target.Height + 5 .ListFillRange = xStr If .ListFillRange = "" Then 'xArr = Split(xStr, ",") Me.TempCombo.List = xArr End If .LinkedCell = Target.Address End With xCombox.Activate Me.TempCombo.DropDown End If End Sub Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Select Case KeyCode Case 9 Application.ActiveCell.Offset(0, 1).Activate Case 13 Application.ActiveCell.Offset(1, 0).Activate End Select End Sub
  • To post as a guest, your comment is unpublished.
    xxxZIDANExxx · 2 months ago
    Hello. The code is very cool. Please make sure that the formula "INDIRECT" is carried out and displayed. It is very necessary
  • To post as a guest, your comment is unpublished.
    Zaher · 3 months ago
    The code is not working for a validation list which is created by vba code and the source is a named range
  • To post as a guest, your comment is unpublished.
    Tony Araya · 3 months ago
    This works incredible! that is exactly what I was looking for, Thank You so much!
  • To post as a guest, your comment is unpublished.
    Elizabeth · 3 months ago
    This works wonderfully. May I ask how you can tweek the macro to work on a whole workbook and not just the workshee?
  • To post as a guest, your comment is unpublished.
    Monse · 4 months ago
    Buenas! Me ha funcionado de maravilla, pero digamos que solo quiero que aplique a una cierta cantidad de listas validadas, no a todas las que se encuentren o agregue a la hoja...como detengo esta función para que afecta a otras lista validadas en la hoja?
  • To post as a guest, your comment is unpublished.
    Arianna · 6 months ago
    How can I update the list? 
  • To post as a guest, your comment is unpublished.
    Edwin · 6 months ago
    Mine disappears when Design Mode is deselected, why?
  • To post as a guest, your comment is unpublished.
    Nat · 7 months ago
    Thank you, this worked for me.
  • To post as a guest, your comment is unpublished.
    Jesus Bermudez · 8 months ago
    Hi, thanks for your code. I have a problem, It's not working. My data for the validation list is in other sheet (Also is a excel table Ctrl + T because I need enter new data every day), I dont know if any of these are the problem. Can you help me with this?
  • To post as a guest, your comment is unpublished.
    stefany · 9 months ago
    hi, thanks for your code. I would like to autocomplete and make multiple selections in a drop down list. Can you help me please?, thank you
  • To post as a guest, your comment is unpublished.
    krish · 1 years ago
    Hello thanks for the code it works great.
    But i want to auto expand drop down list when i move to next cell by pressing enter key without clicking on arrow button (or) alt+down.
    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    taras.samolyuk · 1 years ago
    Hello, is there a chance to add dependency to select date and in the next column to the proposed choices?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Would you provide a screenshot of what you are trying to do?
  • To post as a guest, your comment is unpublished.
    Christiam Alfonzo · 1 years ago
    como hago para agregar esta función
    =INDIRECTO("tabla1[columna]")
    al código
    • To post as a guest, your comment is unpublished.
      Ben · 7 months ago
      @Crystal I'm having the same problem. One of my lists uses an INDIRECT formula (based on what they choose in the first column) to generate the list. With this code, now the formula appears instead of the list.
  • To post as a guest, your comment is unpublished.
    Danielle · 1 years ago
    I tried to use this method. I added the code. After that I add the range needed. Looks all fine. Then I test and the combobox dissapears directly after filling it. So the code works, but its not usable. What do I do wrong?
    Kind regards, Danielle
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      The code works well in my case. It is not easy to figure out the problem as you described above. Would you mind sending your worksheet to my email address?
  • To post as a guest, your comment is unpublished.
    coltan123 · 1 years ago
    Code is working , but data validation is not working. For example, when i set the data validation to be red/bue/yellow, it does not show error message when i type green
  • To post as a guest, your comment is unpublished.
    Mhyk · 1 years ago
    Hi I have some difficulties with my excel work. I would like to know if there is a method to autofill the cell I desire?
    Example
    1 = san pedro
    2 = santa rosa
    3 = calamba
    4 = san pablo
    and so on.

    I have a list with correspoding numbers. If I input number 1 in Cell A5 I would like it to automatically fill the Cell B5 with its corresponding list name (san pedro) and if I input 3 in A5, B5 will be = calamba.
    thank you. for the help, Im having trouble to find a solution to this hope to find answers.
    Thank you and God Bless.


    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Methods in this tutorial can do you a favor:
      How To Auto Populate Other Cells When Selecting Values In Excel Drop Down List?
      https://www.extendoffice.com/documents/excel/2400-excel-drop-down-list-auto-populate.html
  • To post as a guest, your comment is unpublished.
    Sisi · 1 years ago
    Hi Guys,


    I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this?
  • To post as a guest, your comment is unpublished.
    Simona Muresan · 1 years ago
    Hi Guys,


    I have made 2 different drop down list searchable in 1 sheet, the fist is working well but the second drop down list is showing me the formula of indirect as the second drop down list is indirect of the first. I have apply the VBA with combo and so on but still the second list is showing me the formula. how to I fix this?

  • To post as a guest, your comment is unpublished.
    Juan Carlos · 1 years ago
    Hello, I have a problem with this method and I already use a list validated with an indirect function to refer to the range of a table and when applying this macro it shows me only the indirect formula in the list! How could I solve this problem?
  • To post as a guest, your comment is unpublished.
    Leonardo · 1 years ago
    Hi! Is it possible to disable the combo box scrolling list when I click on the cell? and leave it active only if I click on the arrow, thanks!
  • To post as a guest, your comment is unpublished.
    Leonardo · 1 years ago
    Hello, is it possible to replicate this in a complete column? I mean that each individual row of a column has this possibility or at least 1000 rows in a simple way. Thank you!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Leonardo,
      The VBA works for all data validation drop down lists in the worksheet.
  • To post as a guest, your comment is unpublished.
    gurita_22 · 1 years ago
    If you use this method with one combobox/sheet i guess it's flawless.

    I used this method with three comboboxes on one sheet with 3 different ListFillRange.
    Dropdown keep showing up on different combobox than what i'm typing in.
    1. I type and select correctly from the first.
    2. When i go to second combo, the third dropdown pops up.(I have to select the"", as it is still empty)
    then i can search my second combobox.
    3. When i go to the third, the first dropdown pops up, I reselect the only item(as there is only one that was already selected in the first step)
    After this i can add the content in the third dropdown.



    All subs are on sheet1's vba window.
    Now i'm trying to spit code in different modules, but can't really make it work.
    Search works, but i always have to push the dropdown arrow to see available results to select. Dropdown doesn't pop up at all.

    using 365, but i have to make "sheet" work on excel 2010
  • To post as a guest, your comment is unpublished.
    Rohit · 1 years ago
    The command works when first applied. But after saving the file, when i reopen it the drop down itself disappears.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Rohit,
      After applying the code, please save the workbook as an Excel Macro-Enabled Workbook before closing the file.
      • To post as a guest, your comment is unpublished.
        wendy · 11 months ago
        I have done that - saved as a macro-enabled workbook but it still all disappears when you close the file. Thanks, Wendy

  • To post as a guest, your comment is unpublished.
    ahmadzaid · 1 years ago
    I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’


    for info .. I use excel 2010

    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      I tried the VBA in Excel 2010 and it works well.
      The drop down list should be data validation drop-down list which is created by clicking Data > Data Validation....
      • To post as a guest, your comment is unpublished.
        ahmad zaid · 1 years ago
        It's work in cell with list but when i move or tap or select any cell with no list ..the up message talked you about always show..

  • To post as a guest, your comment is unpublished.
    Carlos · 1 years ago
    Hi! Thanks for this code!

    I'm getting a compile error saying "variable not defined", it highlights the "cancel = true" line
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Carlos,
      Which Excel version are you using?
      • To post as a guest, your comment is unpublished.
        ahmadzaid · 1 years ago
        I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’


        fo info. I use excel 2010
      • To post as a guest, your comment is unpublished.
        Carlos · 1 years ago
        Hi. Office 365 Pro Plus
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          Hi Carlos,
          Remove the "cancel = true" line from the VBA, then try again.
  • To post as a guest, your comment is unpublished.
    Mitchell · 1 years ago
    Hi Crystal,

    Absolutely perfect and thank you for the code. However when i press enter the cell automatically goes down. How can I make the cell go to the right when I press enter?

    MItchell

  • To post as a guest, your comment is unpublished.
    Sergio · 1 years ago
    Hi,

    Is it possible to show the matches with the caracter your typping using this code and format of combox?

    Example:

    You have a list with Onion, Black Onion, Green Onion, Red Onion...

    And in the combox you type: "On", so the drop down list show all the values existing on the list that matchs with "On".

    Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Sergio,
      The method in this article can help you:
      How To Create A Searchable Drop Down List In Excel?
      https://www.extendoffice.com/documents/excel/2439-excel-drop-down-list-search.html
  • To post as a guest, your comment is unpublished.
    Reinaldo Yajure · 1 years ago
    When I applied this code to my worksheet, the Undo function is disabled (but only for this sheet in the workbook) -- is there a way to fix this so that undo can still be used?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Reinaldo Yajure,
      Please apply the below VBA code. The problem is fixed. Sorry for the inconvenience.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Update by Extendoffice: 2020/05/19
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim xWs As Worksheet
      Dim xArr

      Set xWs = Application.ActiveSheet
      On Error Resume Next
      If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      If xStr = "" Then Exit Sub
      Set xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      With xCombox
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = xStr
      If .ListFillRange = "" Then
      xArr = Split(xStr, ",")
      Me.TempCombo.List = xArr
      End If
      .LinkedCell = Target.Address
      End With
      xCombox.Activate
      Me.TempCombo.DropDown
      End If
      End Sub
      Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Select Case KeyCode
      Case 9
      Application.ActiveCell.Offset(0, 1).Activate
      Case 13
      Application.ActiveCell.Offset(1, 0).Activate
      End Select
      End Sub
      • To post as a guest, your comment is unpublished.
        Alistair · 1 years ago
        Hi there, this is a great solution, but for some reason, in the original code, the combox was hidden when the cell with the data validation was not active, and only visible when the cell was selected.
        With this code, we get undo back, which is perfect, but the combox does not auto-hide again when you click off.
        Do you have a solution for this?

        Thanks,

        Alistair
  • To post as a guest, your comment is unpublished.
    Sandeep Sharma · 1 years ago
    is there any way to increase the list size as it is showing up only 10 line items as i have 12 lines
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Sandeep Sharma,
      In the Properties dialog box of the Combo box (step 5 of this tutorial), please find the ListRows field and then change the number 8 to 12 under the Alphabetic tab.
  • To post as a guest, your comment is unpublished.
    Jonas · 1 years ago
    How can I Use Tab/Arrows/Enter to move to another cell once the current cell is filled? Thanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Jonas,
      The Tab key and the Enter key can work normally. But can't help with the Arrows yet. Sorry for the inconvenience.
      • To post as a guest, your comment is unpublished.
        V.A. · 4 months ago
        Hi crystal this works great, but I cannot tab or enter to move to the next cell. I have found no command that lets me move to another cell. I have to click the next cell to categorize the column. I have used the code updated 2020/05/19 above in the comments. Please help :)
  • To post as a guest, your comment is unpublished.
    Ramitha · 1 years ago
    Hi . It is a very useful code . However , I cannot use it on horizontal tables which are is a reference for vertical drop downs. Is there a solution ?
  • To post as a guest, your comment is unpublished.
    K1000z · 1 years ago
    Hi Guys, are you able to share a code which i can use so that the autocomplete is used on the same column i.e H in every sheet within the workbook.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      If you only want the column H to be affected, please try the below VBA code. But it is only available in one worksheet. For multiple sheets, you need to add the code to each sheet code window. Hope I can help.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'Update by Extendoffice: 2019/8/14
      Dim xCombox As OLEObject
      Dim xStr As String
      Dim xWs As Worksheet
      Dim xArr
      Dim xRgStr As String
      Dim xRg As Range
      xRgStr = "H:H"

      Set xWs = Application.ActiveSheet
      On Error Resume Next
      Set xCombox = xWs.OLEObjects("TempCombo")
      With xCombox
      .ListFillRange = ""
      .LinkedCell = ""
      .Visible = False
      End With
      Set xRg = Intersect(Range(xRgStr), Target)
      If xRg Is Nothing Then Exit Sub
      If Target.Validation.Type = 3 Then
      Target.Validation.InCellDropdown = False
      Cancel = True
      xStr = Target.Validation.Formula1
      xStr = Right(xStr, Len(xStr) - 1)
      If xStr = "" Then Exit Sub
      With xCombox
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = xStr
      If .ListFillRange = "" Then
      xArr = Split(xStr, ",")
      Me.TempCombo.List = xArr
      End If
      .LinkedCell = Target.Address
      End With

      xCombox.Activate
      Me.TempCombo.DropDown
      End If
      End Sub
      Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Select Case KeyCode
      Case 9
      Application.ActiveCell.Offset(0, 1).Activate
      Case 13
      Application.ActiveCell.Offset(1, 0).Activate
      End Select
      End Sub
      • To post as a guest, your comment is unpublished.
        K1000z · 1 years ago
        Looks good.Thanks Crystal.
  • To post as a guest, your comment is unpublished.
    Billy · 1 years ago
    I get an error message (1004) - the debug highlights ‘If Target.Validation.Type =3 Then’
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Billy,
      Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Angga S.P. · 1 years ago
    Woooooooowwwwww very useful, thankyou for sharing, god bless you
  • To post as a guest, your comment is unpublished.
    Big.Moe · 1 years ago
    Love this! Thanks so much. Got a small issue though. I like to tab from one cell to the next, but when I get to the cell with the drop down list, I can tab into the cell with no problem, but once I'm in, it won't let me tab back out. I have to physically move the mouse over and click into the next cell. Any fix for this? Kind of disrupts my rhythm, which is why I loved this autocomplete function in the first place. ;o)
  • To post as a guest, your comment is unpublished.
    Jerome · 1 years ago
    This is a great find, however, I have one little issue: I like to tab through the cells as I am entering data. I can tab into the cell with the drop down menu just fine, but once I'm in that cell, it stops me from exiting the cell with a tab. I have to physically move the mouse to the next cell and click on it. Is there a fix to be able to simply tab in and tab out of the autocomplete cell? Thanks.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Jerome,
      The Tab key still works well in my case after applying the code. Which Excel version are you using?
  • To post as a guest, your comment is unpublished.
    Alex C · 1 years ago
    I am using this code and a data validation with drop down lists for easy acces to what i need because i need the data to remain in excel boxes for other formulas to use it. However one of the combo boxes has almost 1500 items in the drop down and I'd like the user to be able to start typing a word in the combo box and get a list of suggestions to choose from based on the word entered for example if they type 'joh' in the combo box I want to show them a list of all the choices in the drop down that contains 'joh' so they can select the one they want. Now i can find the first 'joh' but it shows me like a list with what is near this 'joh', not specific all that contain 'joh'. Can i modify this code so can be used for what i want? Thank you and please help
  • To post as a guest, your comment is unpublished.
    Don P. · 1 years ago
    The Combobox works fine when 1-MatchEntryComplete is selected ,when I changed the selection to 2-fmMatchEntryNone in the Combobox properties it did not work ,i.e It does not auto select as I start typing. Is there a fix for this?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Don P.
      Only the 1-MatchEntryComplete option can make it work. Sorry can't help you for that.
  • To post as a guest, your comment is unpublished.
    Christian · 1 years ago
    It works. Thank you and God bless
  • To post as a guest, your comment is unpublished.
    Kuku · 1 years ago
    The code working but whenever I click on cell there the drop list auto prompt ,this make me unable to copy the contents and paste somewhere , how can I enable the copy and paste ?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Kuku,
      When the drop down list auto prompt, you can see the cursor displays inside the cell. It allows you to manually select the contents in the cell and then copy and paste with shortcut keys. Please have a try. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Charlis · 1 years ago
    I try copy the whole cell of droplisting after all selection , but whenever I click on cell it auto show the listing , this has cost me unable to copy the cells and paste the contents somewhere , any solutions , pls help ?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Charlis,
      Actually when the drop down list auto prompt, you can see the cursor displays inside the cell. It allows you to manually select the contents in the cell and then copy and paste with shortcut keys. Please have a try. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Eze · 2 years ago
    Hi! Does Anyone Know, how use this function just on a cell?
    • To post as a guest, your comment is unpublished.
      Thomas · 1 years ago
      Change line 16 to read "If ActiveCell.Address = "{ADDRESS e.g. $C$8}" Then"
  • To post as a guest, your comment is unpublished.
    ron · 2 years ago
    hi! I have tried the code, and it works fine, but it will not allow a macro to copy and paste.
  • To post as a guest, your comment is unpublished.
    RyanPlanteNJ · 2 years ago
    Hey guys, I'm having an issue where it doesn't let me type what I want to search for letter by letter. It just shows the whole word automatically and highlighted, and I start to type it moves it to the next closest word. is there anyway to not have it highlight the whole thing?
  • To post as a guest, your comment is unpublished.
    MartinK. · 2 years ago
    Great tutorial. Thank you.
  • To post as a guest, your comment is unpublished.
    me · 2 years ago
    Doesn't work. Please update.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi,
      Sorry for the inconvenience. Which Excel version are you using?