VBA to help create searchable dropdown boxes for multiple cells (all data 1 column)

  • i'm new to this as you can tell. i have tried/searched numerous ways to create a searchable dropdown list with no luck and 123% pure frustration. i currently have a spreadsheet with 30 or so Data Validation boxes all pulling info from a single column on a different sheet (basically a timesheet where the foreman selects his hourly trucks). It does work, but i have a lot of complaints in the field because the "Truck Owner" field is not searchable. can anyone please help me? thanks in advance. i can send a copy of the sheet

  • Hi,


    Just a couple of remarks :


    1. Your cell C2 does not have to be merged with cell D2


    2. When dealing with cell C10, you are saying "the field is not searchable" ...


    Do you mean you would like to have the Autocomplete feature as you are typing in the drop down list ???

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

  • Hi Carim,


    on #2 i was trying to set up the autocomplete by typing feature for the "Truck Owner" section down in the middle. I was able to set it up and get it to work by turning off the data validation > "error alert" tab. My problem is that when i released it to the field the guys were able to start typing in the dropdown box to quickly find the truck owner, but then wouldnt select, or tabbed out of the cell early causing me to have to go in and fix multiple cells (13-20 timesheets a day.) because the VLOOKUP feature wouldnt pull the "Minority Trk & Truck ID" information to the left that is needed for payroll


    i am trying to find a way to let them type in the "Truck Owner" field quickly narrow down the list, but also have some type of data validation that will force them to chose only from the list. Any help you could offer would be much appreciated

  • Hi,


    Just to be 100% sure your request is clear ...


    You are not after a formula based solution with Data Validation ...BUT a VBA based solution ...


    You know you will not encounter obstacles when deploying your Excel workbook to the field ...

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

  • im not sure of the which method is better for what i'm trying to achieve. I am a decent excel person and have tried to solve by scouring youtube for answers. I have no idea how to do VBA :( i just started trying to learn macros to speed up things like password protecting/unprotecting my sheets and other small novice features. this one i am unable to fix and it has become frustrating

  • .... I have no idea how to do VBA :( i just started trying to learn macros to speed up things ....

    Hi again,


    Attached is a test file ... which could give an idea what you might need ... with the Autocomplete feature


    Hope this will help

    :)

    Files

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

  • Carim,


    that looks like it will work!! do i need to do this 50 times for each box or can i do one and carry down? also is there a video or website that you can suggest i reference? lastly....... was that conditional formatting that turned the "Name" cell yellow when the correct client name was selected? IF i do get stuck and need further paid help can you "recommend" someone?


    Thanks and Merry Christmas

  • Hi again,


    When it comes to a large number of ComboBoxes, you would require to use a class module ...


    But, you could also bypass the class module by using a pop-up UserForm ... and this reminds me of a rather old thread ...


    As soon I have a moment, will dig into my archives ...;)

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

  • Your data sheets would be much better if they followed the rules for working with data in Excel. A table format, no completely empty rows or columns and only one header row. This example shows how you can build a UserForm that will allow the user to search the data and more all within he serForm. he search is extremely fast

  • Hello,


    Once you have tested the " User-Form-Autocomplete " proposal ... feel free to share your comments ;)

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