Creating a popup populated by a partial search

  • Hello,
    I am currently working on a project where I need to build a database in Excel to be able to search in regulatory lists.


    The current setup I have has a worksheet for entering data and showing results based on that and a worksheet with rawdata, and while it works for most purposes I find that a feature that would be nice to have is the ability to search on a) partials and b) names (and not just CAS-numbers as is the case at the moment).


    I can however not for the life of me figure out how to get a popup of possible entries that someone can choose from and pick the entry that they are actually interested in and from there populate the text boxes.


    At the moment there is no option to searchfor text, but I am guessing that a partial search for a CAS-number and a partials search for any other text string will behave in the same way.


    Steps to do what I want:
    1) Enter a partial (or entire) CAS-number into a text box (i.e. 75-25-2 or 75-2) and press "Search" - currently the code doesn't allow this since all you can do is enter a full CAS-number so I made it check that it at least was a correct one.
    2) Get a pop up listing all possible matches
    3) Choose one entry from the pop up (in the above case probable entries would be 75-25-2 and 75-27-4)
    4) Populate text boxes 2-8 with data gathered from the raw data based on the choosen entry


    Edit: Example file have been uploaded as well

  • Re: Creating a popup populated by a partial search


    Winterbay,
    Take a look at the FindAll function here: http://www.cpearson.com/excel/FindAll.aspx


    Incorporating that with a UserForm containing a listbox that holds the results when there are multiple possible matches should answer your questions.


    I have done a mock-up of this using your code in the attached file. One thing you will notice is that the CheckCAS function has had to be heavily modified to allow partial string matches. You may need to consider if you need the original functionality back and/or write two separate functions.

  • Re: Creating a popup populated by a partial search


    gijsmo,
    Thank you very much. I just found a note this morning in one of my macro educational papers about "custom dialog boxes" which turned out to be the userform way. I've started settign it up and will take a look at your code as well.

    I am aware that the CheckCas-function is not suited for this type of search as it is written, but since my colleagues use this tool I though it was better to have a function to check for incorrect numbers while only one entry was possible anyway.

    I'm likely to come back with questions later :)

Participate now!

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