Search command button on Excel Userform to return a list of results

Can you supply a sample workbook with what you have done so far? Also please explain what you mean when you say "X" down that list".

Thank you for your follow up.  What I mean by the "X" is that when the list is open and you want to close it, you (the user) can click on the "X" or maybe a button to close and return to the previous screen, which should be the user form.  I will be attaching my file shortly.

Hi, I'm attaching a sample file.
SAMPLEFILE.xlsm

OK, right now, as far as I can tell, when the user searches he doesn't get a list of results but rather the info for one row in the worksheet. If that's not correct then please explain how to see a list. If I'm correct then do you want a list to appear on the userform and allow the user to select one of them?

I'm confused about

If the user does not find a result they are looking for, they should be able to "X" down that list and return to the userform.

Aren't they already using the userform, so what do you mean by "return to the userform"?

Hi, the Search button was originally programmed to return the results to the userform. But I later realized it's better to have all the results returned in a list. I guess I should have removed the current code for the Search button. So what I'm wanting to do is program the Search button to display a list of the search results. The user should be able to click on a row and have that record displayed in the userform. If the user doesn't find what they are searching for they will need to close out of that list and only have the blank userform displayed. I hope I'm making sense!

So to start with the user brings up the userform. I know that the userform shows up when you open the workbook but what if they close the userform and want to do a second search?

The user selects the search criteria and clicks search. New code will show the info from the matching rows in the userform.

If I'm right so far then I'm confused when you say "the user should be able to click on a row [in the userform?]  and have that displayed in the userform" because isn't it already there?

Well what I'm envisioning is something like a database I'm currently using. The search results are displayed in a different form like a table with rows and columns. The table will only display the staff name, and client first and last name and recipient first and last name. They would then click on a row in the table to display all the data into the userform. The reason for this is because they are either trying to open an existing record or they are searching to see if a record exists. So the table will show all the choices.

I originally started out with the Search button returning the first result into the actual form, such as what you are seeing. However, the problem with me doing it that way is that I then needed to program another button to continue searching the worksheet for the next match.  The clicking through all the possible choices could be endless.  To illustrate this, say the user is looking to see if they've ever had contact with a Jane Doe.  Let's assume the worksheet contains three records with the same name, but they all live in different areas.  So they enter Jane Doe under Client First Name and Client Last name and then click on Search.  It will bring up the first Jane Doe, but in order to see if there's more matches, they will have to click on another button called Next Record.  I originally had my userform set up that way, but could not program another button to keep searching through the whole worksheet.  So I decided that it would be best to return all the Jane Does at once by way of a table, which is different from the userform.  The user could look at the list with a quick glance and see the record they are looking for.  They will then click on the row from that list and it will automatically open up in the userform and close down the list.  They can then make changes to that record (and I will have to create a button that will save the changes back to the worksheet in its original place instead of creating a new record on the next empty row.)  If they make no changes, they can still click on a button to "Save and Exit" or "Save and Add New Record."  If they wish to perform a new search, they will use the Save and Exit button that closes down that user form and opens up the blank one with the original search button.

If you have a suggestion on how to make this process easier, I'm open to it.  In a nutshell, what this database is for is to record every contact with a client.  They should be able to search to database for existing records for a particular client and just add more notes to it.  If none exists, they enter a new record.

Am I making this harder than it needs to be?

Take a look at my DatabaseForm,, it has a search feature that lists results into a ListBox on the form and each item can be selected and amended if required.

Katie, I don't think you need the userform at all. Take a look at the attached workbook. In it, on the Calls sheet, I've added Data Validation for STAFF NAME, HOW DID CLIENT CONTACT and TYPE OF RESPONSE. There are other columns where Data Validation could be used but I'll leave that to you. I also added filters for all the columns. The latter will let you do your searching and the former will assure that only valid data is entered.
28973367.xlsm

Hi Martin:

Thank you for the sample workbook.  I failed to mention the whole reason for this userform, and I apologize for that.  

I originally created just the worksheet with the data validation as needed for the columns, just like the sample you sent.  In fact, that is what my co-workers are currently using.  The problem is that there are over 30 columns and the incessant tabbing to get to certain columns (or having to scroll) is beginning to wear their patience a little thin, especially when you consider they are entering over 200 new entries at a time.  They don't have to complete an entry in every column, so there's quite a bit of tabbing and scrolling involved.  I've assured them that this worksheet is just temporary until I can get this userform figured out so they will have all the fields visible on the screen.  

I guess you can say that I'm the gatekeeper for the reporting, so as long as I have the worksheet I can gather any data requested.  When this project is finished, the idea is that no one else will be able to see the worksheet in the background, but me.  They will only have access to the userform and the associated functions with the command buttons.  I've tried several codes for the Search button, but they all seem to rely on a unique identifier to search by.  One video involved three buttons, one to search the first time and then another button to search forward and another button to search backwards.  That's why I was wanting to have the search results displayed in a listbox instead of a button farm.

I've been working on this for four months, so figure by the time I get this working like it should the staff will have retired by then.  But now it's become a goal of mine, to see it finished and working.

I have not opened Roy Cox's attachment yet.  But will do so soon!

I don't think that the userform saves a lot when it comes to tabbing since in the userform to get to the next field you either tab or directly select the field that you want and that's exactly the same as direct entry on the sheet. However, maybe you should consider using Access rather than Excel since all you seem to want is a data entry form.

Are you planning on sharing the data entry? If so then Excel is definately not the right choice.

Yes, all staff will be using it.   I know one of the major drawbacks is only one person can be using it at a time. The only workaround I could think of is to provider each staff person with their own file. I would then have to merge all the worksheets into my master copy so I can run monthly reports.

I did try getting this set up in Access, but since I have no knowledge of Access I hired a family member to build it. However, they hit a point where they were unable to troubleshoot the bugs even after seeking help from several Access forums. So it was tabled. I just don't think I can start over with learning something new again.

Post your problems in the Access topic area of EE. I'm sure someone will be able to help.

The "solution" essentially wound up being "you can't do that" and it was my posts that pointed that out so I believe I should get credit.

There does not seem to be a way to object any more so I'll do it this way. "You can't do that" is an acceptable answer and so my https://www.experts-exchange.com/questions/28973367/Search-command-button-on-Excel-Userform-to-return-a-list-of-results.html?anchorAnswerId=41874830#a41874830 should be accepted.