Solved

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

Posted on 2016-09-29
21
42 Views
Last Modified: 2016-11-13
I have built a userform that has three command buttons - Search, Save and Add New Record, Save and Exit.  I am struggling with the Search function.  The challenge is that there are 33 fields, but only five would be suitable to search by:  Staff Name, Caller First and Last Name, Recipient First and Last name.  Since the records do not have a unique identifier, the end user should be able to search by any of those five fields and get a returned list of matches.  

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.  I need to figure out a way that when they "X" it down, it will have cleared out the search data and display only a blank userform.

If the user clicks on a name in the list of results, it should then open that record with all the associated data into the userform. I'm thinking I should create another Userform just for this function so the code for the Save and Exit Button as well as the Save and Add New Record button will replace the record on the worksheet instead of adding another record to the next blank row.  I have read through every possible code I can find on the web because I'm not a programmer and couldn't possibly figure this out on my own.  I do have the other two buttons working though, thanks to the web.

Can anyone help get me started on the Search button programming so that it returns the results in a list?  I'm thinking there is code out there that will tell me how to get the record to display on the userform as well as a code to "X" down the list of results.
0
Comment
Question by:Katie Dee
  • 9
  • 8
21 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41822448
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".
0
 

Author Comment

by:Katie Dee
ID: 41822562
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.
0
 

Author Comment

by:Katie Dee
ID: 41822660
Hi, I'm attaching a sample file.
SAMPLEFILE.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41822724
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"?
0
 

Author Comment

by:Katie Dee
ID: 41822749
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!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41822758
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?
0
 

Author Comment

by:Katie Dee
ID: 41822771
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.
0
 

Author Comment

by:Katie Dee
ID: 41822827
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?
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41822927
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 41824287
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
0
 

Author Comment

by:Katie Dee
ID: 41824355
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!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41824381
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41824418
Are you planning on sharing the data entry? If so then Excel is definately not the right choice.
0
 

Author Comment

by:Katie Dee
ID: 41824426
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41824757
Post your problems in the Access topic area of EE. I'm sure someone will be able to help.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41874830
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.
0
 

Accepted Solution

by:
Katie Dee earned 0 total points
ID: 41874858
Just an update to say that what I ended up doing was adding two command buttons to the actual worksheet.  When the workbook was opened, I had all the fields, but one, protected.  The user could then add additional comments to the record or they could click a command button to add a new record, which would open the blank user form.  They could then complete the form and save and return to the worksheet or save and exit Excel.  What this did was alleviate the need for a search button to open up a list.  But opening up the worksheet on startup, they could simply use the "Search and Find" tool to see if an existing client was on the worksheet.  If they were, all they are allowed to do is add additional comments, but the key data was protected so it could not be changed.  This is in its second week of use and everyone is loving it.  So my solution was a workaround.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41879507
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#a41874830 should be accepted.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now