Find a name from a list of names

Hi.
I have 500 Computer Club Members' names on a worksheet.

About 50 visit the Club on a particular day.
I have to make a note of who attends each day.

To avoid having to search through the full list of 500 Members, as a Member arrives I need a macro to pick out the name of that member from the full list...... preferably as I start to type their name

For example......if the member was GRAY... Morris, I need the macro to list everyone of the name "GRAY"......reducing the possible names as each new character is typed (just like entering characters in "To" in 'email recipients')...but finally listing ALL the Members named "GRAY"

I then need to select the correct member (eg. Morris or Margaret) and selecting that person to enter them on a daily attendance sheet.

Any help greatly appreciated........I understand Macros, I have written a few, but this is beyond my ability.

Many thanks in advance.
Morrisg

Maybe something like the attached example?

Quote:

---

Originally Posted by jolivanes

Maybe something like the attached example?

---

Wow!!
Absolutely what I was looking for. Almost perfect.
The only thing that needs amending is to show Surname and FirstName, so that if there are 2 people named "GRAY", it shows "GRAY Morris" and "GRAY Margaret". I can then select the correct one.
Surname is in Column A and Firstname is in Column B.

I think I could have made that change myself but I can't access the code.
As a newbie to this Forum, is it acceptable to ask for the code that someone else has produced?
I would like to see how it's done.
I'm the President of JaveaComputerClub in Spain and would like to show members how the solution was achieved.

Many thanks........I'm over the moon.
Morris

give this a try:
You can access the code by Alt-F11

Thanks JKwan. I was also thinking about concatenating as an alternative but this is better.

I'm grateful to both of you......JKwan and jolivanes.
Are you really doing this at 6.30 in the morning? It's 17.00 here in Spain.

One thing I'm still stumped with.........I used Alt + F11, which brings up the VBA info..........but it shows only..............
Sub Show_UserForm1()
I know the answer is simple (I accessed it in the first version) but how do I access the Macro that shows the full code?

Sorry to trouble you with this.
Regards
Morris

Yes, 6:30am, that is the time I start work.
If you right click on user form, you then select View Code

Got it!
ALT + F11........click on "User Form 1.........when the image of the UserForm appears.........dbl click the top left box (where the input is entered)........BINGO.
For your info.....we open 3 days a week. We have volunteer ladies on our desk at the entrance. They have a printed, alphabetic sheet of all 500 members, a new list each week. As each member comes in, they have to find their name and put a tick against it. A laborious job..........but no longer, thanks to 2 guys (?) in Canada..
Muchas Gracias.
Have a good day.
Morris

Quote:

---

Originally Posted by jolivanes

Maybe something like the attached example?

---

Almost certainly my last query.......
Is it possible to empty the right hand box of the suggested names..........as soon as one of them has been clicked to select it..........and at the same point, to clear the characters in the input box (top, left).........so that the UserForm is empty, waiting for the next input?

Gracias
Morris

It's nice to hear you have it all under control.
Change the code you have for ListBox1 to this.

Code:

---

Private Sub ListBox1_Click()
    Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1) = ListBox1
    Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = ListBox1.Column(1, Me.ListBox1.ListIndex)
    TextBox1.SetFocus
    ListBox1.Clear
    TextBox1 = ""
End Sub

---

The

Code:

---

ListBox1.Clear

---

is not required I think. Try it with it and without it

BTW, where are you in Spain. I lived there for 8 months (Tarragona)

I have lived in Javea, half way between Alicante and Valencia, for the last 20 years.
I drove past Tarragona a few days ago on my way back from visiting my daughter near Val d' Isere in France.

JaveaComputerClub is the largest computer Club in Europe.
Last Thursday we had a presentation about Artificial Intelligence.............scary!!
Robots that know more than we do.

Regards and thanks.
Hasta Luego.
Morris

Can I suggest a slight change to JKwan's code to improve the selection?

Code:

---

Do
                If UCase(Left(c.Text, Len(TextBox1.Text))) = UCase(TextBox1.Text) Then
                    ListBox1.AddItem c.Text
                    ListBox1.List(ListBox1.ListCount - 1, 1) = c.Offset(0, 1)
                End If

---

Well, the previous one was 'nearly' my last request.
Could you please amend the code to add an extra Column (C) which would display each members' membership number.
Then I'll leave you in peace.
Many thanks for everyone's efforts.
Morris

Try this

@JKwan
I looked at this before, but never came up with this neat solution.

In the

Code:

---

Private Sub TextBox1_Change()

---

code, you might want to change

Code:

---

ListBox1.ColumnCount = 2

---

to

Code:

---

ListBox1.ColumnCount = 3

---

and add

Code:

---

ListBox1.ColumnWidths = "75;40;30"    '<---- Find proper widths to show all info

---

below it
Or set the ColumnWidths in the Properties for the ListBox