Solved

In Excel how can I incrementally match a valid list of names as I type?

Posted on 2010-11-19
28
418 Views
Last Modified: 2012-05-10
When using a list for validation, a non-valid entry is only apparent after I type the whole entry.  Is there way to search the list for matching and display valid matches as I type, so that I don't have to type the whole string, and also, flag errors before the complete non-valid string is entered?
0
Comment
Question by:John Fistere
  • 10
  • 10
  • 7
  • +1
28 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
If you use control dropdowns instead of data validation this will work.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
In the properties for the control set listfill range to the validation list, linked cell to the cell that should accept the data and change style from fmStyleDropDownCombo to fmStyleDropDownList so they can't enter invalid things.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Where is the Excel List?

In the Worksheet or as Tommy Suggested in a Combobox?
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
JFistere,

The easiest way is to use Data Validation - after all that's what it's designed to do - validate the data and prevent incorrect entries.

In the attached file I have placed the list of possible names on Sheet2 and named it as a dynamic range. Have a look at Insert/Names/Define for how that is done. That means that you can add names to the list and they will be automatically added without you needing to do anything. I have also placed the macro shown below in the ThisWorkbook VBA module, so the names are sorted automatically every time the file is opened.

Apart from that I have in the Data Validation section added a simple alarm if the user attempts to enter a name not in the Data Validation List.

Hope that helps

Patrick
Private Sub Workbook_Open()
Dim rng As Range

With Sheets("Sheet2")
    Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    .Activate
End With

rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
Sheets("Sheet1").Activate
        
End Sub

Open in new window

data-validation-01.xls
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
I haven't had a chance to look at any solution but the first.  When I go to Developer / Insert, Combo Drop Down and Combo List are disabled  (along with Text Field).  The other Form elements seem to work, but are not what I need.  I looked for a spreadsheet option to change, but I couldn't find a likely choice.  I'm going out now, but I'll be back in a couple of hours.

John
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Siddarthrout,
The names are in the worksheet.

Patricklab,
I sorted the list initially and add names in their sorted position.  I think what you suggest will identify invalid names only when entry is completed, and will not display complete valid names when the first few letters are typed.  Is that correct?  The only macro/sub experience I have is memorizing macros.  I would like to try your suggestion but I will need more of a step by step guide to incorporating your sub.

I am still looking for a way to select and insert the control that Tommy suggested.

John
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
John,

> I think what you suggest will identify invalid names only when entry is completed, and will not display complete valid names when the first few letters are typed.

With Data Validation you should not type anything at all. You should ONLY select names from the dropdown. If that meets your needs then I will take you through how it all works.

However my method does not depend upon a macro. The macro only sorts the list of names when the file is opened so that they are easy to find in the dropdown list. You can still use Data Validation without the macro - it just isn't so smart and requires some effort by you, whereas with the sorting macro it makes it easy to maintain the list of names in a logical order without your intervention.

Patrick
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Patrick,
The list is about 100 names long, too long to use a drop down list for selection.  I want to be able to type a few characters and see the first matching valid name come up.  If it's not the one I want, I keep typing.

Thanks,
John
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Yes, the dropdown will do exactly what the asker is trying to accomplish. I like data validation too, but we need to get the asker what she wants.
That said, I'm not sure why the dropdowns aren't available. They should be. It's standard. Could try reinstalling Excel. Anyone else know why this would happen?
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Tommy,
I'm not sure if you had seen my last comment when you wrote yours.  The dropdown list is too long to be convenient.

Cheers,
John
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Perhaps I should add that I am using a drop down list now, and that is what I am trying to get away from.

John
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
No no. It calls itself a 'dropdown' and it will drop down but if you set it up like I said, it will do exactly what you want.
See the example. Click on the dropdown and start typing and it will move you down in the list to what you're typing.
The MatchEntry property should also be set to MatchEntryComplete, but that's default.
names.xls
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Well, Tommy, I finally understood what you were suggesting.  I would like to discuss it more.

In the attached workbook, sheet "Current Approach" gives an idea of what the  actual requirement is.  We have a list of potential advertising sellers, and a list of actual ad sales with names and other information, such as the value of the sale (not shown).

In "Possible Approach" I use your method, but there are drawbacks.  Setting up 150 dropdowns is more labor than I would like, and two columns of the same name is not great.  (I just realized I place the dropdowns directly over the names in the cells so only one column is seen.)   But it's still a lot of work to set up 150 drop downs.  Is there a way to propagate the "LinkedCell" property automatically?

Maybe we're getting there.

Thanks,
John

AdSales.xls
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Tommy,
One other question:  Is there a property or other method to cause entry to be complete when the Enter or Tab key is pressed.   As it is now, you have to mouse-click away to complete the entry, which is not convenient.  Ideally there should be a tab sequence that is functional.

John
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

Expert Comment

by:patrickab
Comment Utility
John,

>Perhaps I should add that I am using a drop down list now, and that is what I am trying to get away from.

Please explain why you are wanting to get away from using a dropdown. Is it because you find it difficult to find the name that you want?

Patrick

0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
John,

I have been looking at your workbook and I have yet to understand why you're doing it the way you are.

You say:

"We have a list of potential advertising sellers, and a list of actual ad sales with names and other information, such as the value of the sale (not shown)."

So what is to stop you from simply entering the information against an individual's name in a fresh column for each new entry?

I cannot for the life of me see why you need to use ComboBoxes at all. They are awkward to set up and offer nothing to the ease of use or anything else for that matter. They are virtually useless in my view. If you want a better way of doing the whole thing then I believe it would be sensible to use UserForms and some VBA to manage a simple Excel database. That would make data entry simple and standardised.

Meantime attached is an Excel file with a sort of auto-entry dropdown, devised I think by Rorya. Also attached is a Word file explaining how it's set up. I don't personally find it functional but it might help.

Patrick
autocomplete-02.xls
Alternative-to-the-Auto-Complete.doc
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Hi Patrick,
I will take a look at your references, but first I want to answer a question you asked.

You wrote:
So what is to stop you from simply entering the information against an individual's name in a fresh column for each new entry?
One reason is that we need a list of sold ads, one row for each ad, to support the publishing phase.

Another reason is that people sell anywhere from zero to ten ads, and and an ad entry requires about ten columns, including business name, ad status, size, price, check #, notes, etc.

I don't want to burden you more than you want, but perhaps you are curious about the actual workbook, so I have attached it.  The data is entered on the Sold Ads page, and essentially all the other pages, including the graph, is created from that information.  Please don't feel obligated to bother with it.

Cheers,
John

Adv--2010-Winter-Program-Ads-Cho.xlsx
0
 
LVL 2

Author Comment

by:John Fistere
Comment Utility
Patrick,
I looked at autocomplete-02.xls:

1. One problem is that it doesn't use the list for validation, just two enumerated names.  That is easily changed to the list.  No problem.

2. The other problem is that it doesn't autocomplete.  In fact, it behaves just like my spreadsheet, which lets you know after you have finished typing the complete name, if the name is allowable.

This makes me think that if it autocompletes on your machine, I have an option set wrong, and if I fixed that, it would autocomplete on my machine.  Any idea where that might be?  I've looked before, and I'll look again.

I also looked at Alternative-to-the-Auto-Complete.doc.  At this point, I'd still like to work towards an autocomplete solution.  But thanks.

Cheers,
John

0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
John,

I've yet to look at your workbook, but thought I'd respond on the 'auto-complete' function. As I mentioned I do not find it helpful as it's awkward to use - but then that's probably just me. There's no extra setting to make it work better - it is what it is!

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
John,

To make it rather easier to select a name in column B of 'Sold Ads', I have completely removed the DataValidation dropdowns and instead installed a UserForm with all the possible names loaded into it. That makes far more names visible at one go and should speed up data entry. The UserForm only becomes visible when a cell in 'Sold Ads'!B4:B99 is selected. It's all in the attached file.

It may not be the solution you were hoping for but hopefully it makes your workbook a little faster to use.

Patrick
In UserForm1 VBA code pane:

Option Explicit

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Selection = ListBox1.List(i)
    End If
Next i
UserForm1.Hide
Unload UserForm1
End Sub


Private Sub UserForm_Initialize()
Dim rng As Range
Dim celle As Range
Dim i As Long

With Sheets("Ad Sellers")
    Set rng = Range(.Cells(3, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each celle In rng
    UserForm1.ListBox1.AddItem celle
Next celle

End Sub


In the 'Sold Ads' VBA code pane:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

With Sheets("Sold Ads")
    Set rng = Range(.Cells(4, "B"), .Cells(99, "B"))
End With

If Not Intersect(rng, Target) Is Nothing Then
    UserForm1.Show
End If

End Sub

Open in new window

jfistere-01.xls
0
 
LVL 45

Accepted Solution

by:
patrickab earned 300 total points
Comment Utility
John,

I have updated the file so that:

1. You can change the selected cell in 'Sold Ads'!B4:B99 whilst the UserForm is visible, without first having to double-click a name.

2. Made it so that when you type the first letter of a name in the selected cell in 'Sold Ads'!B4:B99 the cursor automatically moves to that part of the ListBox on the UserForm with names starting with that letter.

Patrick
jfistere-02.xls
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Yes the UserForm solution is good. Patrick/John, in the properties for the listbox you should set MatchEntry to fmMatchEntryComplete so you can type more than one letter of the name and it will keep moving down to the right person.
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 200 total points
Comment Utility
Although, I think you are looking more for something like this.
You can see what you are typing so you get the Auto-Complete look, but it forces them to only choose names from the list.
You can accept the name with Tab or Enter and another Enter will close the UserForm.

Let me know if you want any other behavior. With userforms you can do almost anything.
jfistere-02TS.xls
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Tommy,

It would be nice if you at least acknowledged that you have highjacked my work. Sure enough you have made some changes but all the same...

Patrick
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
My bad, Patrick. I thought it was fairly clear that I was just modifying your Userform solution to add a few features. I didn't mean it as ahighjack, just a modification.

Sorry. Didn't mean to offend.
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
Tks - :)) - Patrick
0
 
LVL 2

Author Closing Comment

by:John Fistere
Comment Utility
I really appreciate the attention you ggaveuys  to my problem.  Patrick, you took a real good look at the probem and came up with the basic approach.  Tommy, you made it convenient enough to use.  Thanks to both of you.  I would like a little more explanation, and I'll go for that in the main thread.  It's not quite incremental spelling when I run it, so I said it is a partially complete solution.
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
John,

>It's not quite incremental spelling when I run it, so I said it is a partially complete solution.

That's as good as it's going to get.

Meantime thanks for the points.

Patrick
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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