Solved

Create an excel user-form to display filter results

Posted on 2016-10-25
13
47 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 17

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 45

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 17

Expert Comment

by:Roy_Cox
ID: 41860668
Take a look at my FilterForm example
FilterForm--1-.zip
0
 

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 17

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 17

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 17

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 17

Accepted Solution

by:
Roy_Cox earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 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

13 Experts available now in Live!

Get 1:1 Help Now