[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Create an excel user-form to display filter results

Posted on 2016-10-25
13
Medium Priority
?
792 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

650 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