[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1177
  • Last Modified:

Create an excel user-form to display filter results

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
Reyesrj
Asked:
Reyesrj
1 Solution
 
xtermieCommented:
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
 
Martin LissRetired ProgrammerCommented:
...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
 
Roy CoxGroup Finance ManagerCommented:
Take a look at my FilterForm example
FilterForm--1-.zip
0
Technology Partners: 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!

 
ReyesrjAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
Have you checked my form, adding a button to post to the worksheet would be simple
0
 
ReyesrjAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
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
 
ReyesrjAuthor Commented:
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
 
ReyesrjAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
That is basically what my form does. Attach an example workbook and I'll look at it later.
0
 
ReyesrjAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
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
 
ReyesrjAuthor Commented:
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

Technology Partners: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now