Find a name from a list of names

[morrisg]

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

[jolivanes]

Maybe something like the attached example?

[morrisg]

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

[JKwan]

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

[jolivanes]

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

[morrisg]

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

[JKwan]

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

[morrisg]

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

[morrisg]

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

[jolivanes]

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

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

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)

[morrisg]

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

[mdmackillop]

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

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

[morrisg]

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

[mdmackillop]

Try this

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

[jolivanes]

In the

Private Sub TextBox1_Change()

code, you might want to change

ListBox1.ColumnCount = 2

to

ListBox1.ColumnCount = 3

and add

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

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