Macro for Data Validation AutoComplete

  • I have a List of Items in Sheet1 say around 3000 items, in column "B"


    In Sheet2 in entire column "D" I want to create a Macro in such a way that when I type any Letter Eg. "A" it should display all items starting with Letter "A", after finding all options from Sheet1 from column "B"

    Then after "A" if I type "AB" it should show all items beginning with word "AB"

    Then "ABC" should show "ABC"

    This is because if there is a spelling mistake it will sound alert.


    For Example The system should work the same way when we type a formula "=s" it shows all the combinations for "=s"


    I know this will work with data validation, But I want to create a Macro.

    Because creating Data Validation and copying in entire column D will make the file large.

  • Hi,


    In order to better visualize your constraints, would be great if you could attach a sample file with, say, 10-15 records .... :)


    You seem to say it would be a substitute for Data Validation ... so your expected results is also important ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks a lot for your sample file.


    Will take a look at the easiest possible solution :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • One more request there should be only One Macro for substitute for Data validation should be applicable for entire column in "D" in Sheet2 and not a Macro for each Cell individually.

  • OK ... got your point ...


    Instead of a drop down validation list, may be you could use a combo box which allows the Autocomplete feature you are looking for


    See following explanation :

    Excel Data Validation Combo box Click


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I am aware of that function also but the combo box I would then have to scroll down and search for the word in my list.

    Where as typing and getting the list of all combination of the letter would be much faster and easier.

    As said in my first post "=s" would display all the combination of "=s"

  • Attached is your Test file with the AutoComplete feature thanks to the ComBoBox ;)


    Your reference list gets filtered as you are typing letters, with the idea to give you a "Google-type-research" mode ...


    Hope this will help

  • For illustration purposes, you can take a look at the YouTube video Debra Dalgleish at Contextures has posted :


    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim

    Changed the title of the thread from “Create Dropdown With Macro and not Data Validation” to “Macro for Data Validation AutoComplete”.
  • Wonderful.

    Thanks

    Thanks for your Thanks :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!