+ Reply to Thread
Results 1 to 15 of 15

Auto Complete Text from list

  1. #1
    Registered User
    Join Date
    07-19-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Auto Complete Text from list

    Hello, I have a complete column in which I want a list of values to appear. I used data validation to a complete column, but the problem was that there were a lot of values in the list that scrolling through those took a lot of time. So I wanted to use something to autocomplete what I typed from the list of values. I used combo box and it worked fine. But then what the problem was I wanted it to be used on each row of a column separately. by this method it was being used only in one cells and not all the cells in a column. Please help me with the same as its urgent. Thanks.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,094

    Re: Auto Complete Text from list

    Welcome to the forum.

    Have a look here: https://www.extendoffice.com/documen...ocomplete.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,094

    Re: Auto Complete Text from list

    Marc - the OP is new today, so you will need to provide the cross-post link for them, please (see footnote A in the forum rules). I would do it, but I don't know where you have found it.

    Thanks.

    EDIT: https://chandoo.org/forum/threads/au...e-cells.42163/

  5. #5
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Auto Complete Text from list

    I suggest using a combobox that can appear and hide automatically.
    I created a combobox that has 2 special behavior:
    1. The combobox can appear and hide automatically when you select a cell in a certain range.
    2. You can type a keyword in the combobox and the results will be narrowed down as you type.

    So you only need 1 combobox for all cells that need to be filled in.
    Let me know if you're interested in this approach.
    Here's a sample workbook:

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,094

    Re: Auto Complete Text from list

    That's the same approach as the one I linked to in post #2.

  7. #7
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Auto Complete Text from list

    Well, it's a bit different.
    My approach is:
    1. It is only using the combobox while the one that you pointed to is using both data validation & combobox.
    2. The list in the combobox will be sorted, unique & has no blank.
    3. The searching part is more flexible, because it uses the search pattern *word*word.

  8. #8
    Registered User
    Join Date
    07-19-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Auto Complete Text from list

    Thank you so much Mark and AliGW for letting me know I will make sure to do this from next time.
    Last edited by haarleen; 07-20-2019 at 01:22 AM.

  9. #9
    Registered User
    Join Date
    07-19-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Auto Complete Text from list

    Thank you Akuini for the help. I will try this out.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Auto Complete Text from list

    Quote Originally Posted by Akuini View Post
    2. The list in the combobox will be sorted, unique & has no blank.
    Well, this will raise the run time error when the list contains multiple data types, like string, number.

  11. #11
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Auto Complete Text from list

    Ah, you're right, Jindon, this will raise the run time error when the list contains multiple data types, like string, number.
    Hopefully that's not the case with OP's data set.
    But if that's the case then we can change this line :

    in Private Sub ComboBox1_Change()"
    change this:
    Please Login or Register  to view this content.
    to this:
    Please Login or Register  to view this content.

    in Private Sub ComboBox1_DropButtonClick()
    change this:
    Please Login or Register  to view this content.
    to this:
    Please Login or Register  to view this content.

    Thank you for pointing that out Jindon.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Auto Complete Text from list

    No, that's not how it should...

    i.e
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    07-19-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    7

    Re: Auto Complete Text from list

    That explains the error! Its working just fine now. Thank you jindon and akuini for helping me out.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    88,094

    Re: Auto Complete Text from list

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. And please don't ignore our rule on cross-posting in future. Thanks.

  15. #15
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: Auto Complete Text from list

    Quote Originally Posted by jindon View Post
    No, that's not how it should...

    i.e
    Thank you Jindon, for your very valuable input. I really appreciate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto Complete Drop Down List
    By santhire in forum Excel General
    Replies: 0
    Last Post: 03-29-2016, 01:59 AM
  2. Replies: 0
    Last Post: 12-16-2015, 05:50 AM
  3. [HELP]Auto Complete Text from the List
    By jamzsaludares in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2014, 04:21 AM
  4. auto complete from Drop down List
    By hel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2010, 12:10 PM
  5. auto complete within a drop down list
    By gaelforce in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2007, 07:10 PM
  6. auto complete in list
    By sedonovan in forum Excel General
    Replies: 6
    Last Post: 06-26-2006, 01:30 PM
  7. [SOLVED] Auto complete in a validation list
    By Soni in forum Excel General
    Replies: 1
    Last Post: 04-17-2006, 12:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1