[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Create an excel user-form to display filter results

Posted on 2016-10-25
13
Medium Priority
?
1,564 Views
Last Modified: 2016-10-29
Hi All,

I have an excel spreadsheet with a list of names and member ID numbers.  I am trying to create a vba user-form to lookup the members ID by searching for the member’s name.  An example would be in a textbox I type “John” and in a list box it will only display all the names that contain the word “John”.
Also, if I click on the one of the names, it will copy the member ID to the spreadsheet.

I attached my excel workbook.

Thank you in advance.
Form.xlsm
0
Comment
Question by:Reyesrj
13 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 41859884
This code would do that for you
http://www.excelcampus.com/tools/find-all-vba-form-for-excel/

It was a great find
If you want help to tweak it, please let me know
0
 
LVL 51

Expert Comment

by:Martin Liss
ID: 41860290
...it will copy the member ID to the spreadsheet.
Which sheet, and where on the sheet?

Does there need to be an exact match for what the user enters in the textbox?

Do you want to search just the first names?
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41860668
Take a look at my FilterForm example
FilterForm--1-.zip
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 

Author Comment

by:Reyesrj
ID: 41861413
Hi All,

This is some really good stuff!

Xtermie, this seems to be what I'm looking for.  I'm going to try to tweak it myself but, if I need help I will definitely ask.

Martin,

I already started the userform in the visual basic section on the excel attachment.
Once I get the user form to display the list of names and member Numbers I'm looking for, I want only the Member number to be copied over to the next available cell in column A on the Entry sheet tab.

I hope this makes sense.

Thank you all for your help and suggestions.
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41861591
Have you checked my form, adding a button to post to the worksheet would be simple
0
 

Author Comment

by:Reyesrj
ID: 41861612
Hi Roy,

Yes, I saw your form and I'm not sure how it works.  There's too many options.  I Just need a simple user form to search for data and return the selected data to the next available cell in column A.

The coding, I need, maybe a little more complicated because the search will be looking for a name and the value copied to the cell will be a number.
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41862512
So how is the name related to the number?

If you are not filtering the data but merely searching then look at the VBA .Find method. Maybe as on my DataBaseForm example. It's free to download from here
0
 

Author Comment

by:Reyesrj
ID: 41863178
Roy,
That is an awesome user-form!

On my excel workbook, in the All Members tab, column A has Member ID and Column B has member names.

What I'm trying to do is,  when the user does a searches and finds a name the user will click on the name and only the Member ID will be copied over to the next empty cell in the Entry tab on column A.  Something like a vlookup.

I chose this way because we have sign in sheets where the user enters member numbers only and the rest of the information is populated with the vlookup formula.  A lot of times users don't remember their number and just write in there names.  So they search for the name and manually enter the member number.

Your user form, from the link you provided, is something I would like to use.  I has a multiple criteria search option.
0
 

Author Comment

by:Reyesrj
ID: 41863310
Hi All,

For now, I need something simple.
(I hope this is simple)

1.      A VBA user-form with a textbox and a list box.
2.      The textbox is to type a word or part of a word to search for.
3.      The search will look for the word or part of the word in sheet2 in column B.
4.      The list box will display the results of all data that apply.  It should display column A (Member number) and Column B (Member Name).
5.      In the list box, I would like double click on the data I want and only the member number (in column A) gets copied into the next available cell in column A on Sheet1.

I hope this is simple
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41863391
That is basically what my form does. Attach an example workbook and I'll look at it later.
0
 

Author Comment

by:Reyesrj
ID: 41863492
Thanks Roy,

The All Members tab has the data to look up.
The Entry tab is where the data is to be entered.

I started the form, it's in visual basic on the Developers tab in excel.
Form.xlsm
0
 
LVL 22

Accepted Solution

by:
Roy Cox earned 2000 total points
ID: 41864931
I have created a userform that should do what you want.

All Properties of the controls are set on initializing the form.

The Find button is disabled until TextBox one contains an entry, this will prevent the user clicking it before the entry is ready.

Once the textbox has an entry, e.g. cam, the ListBox displays only names containing cam.

Double click an entry and the specified cell will be populated with the member number. There is optional code to add the member name.

You could have further controls to add the Class and dates, etc
Form--1-.xlsm
0
 

Author Closing Comment

by:Reyesrj
ID: 41865148
Roy,

This is perfect!
Exactly what I'm looking for.

Thank you for your time and being so kind to a newbie.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

591 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