Solved

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

Posted on 2010-11-19
28
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 7
  • +1
28 Comments
 
LVL 37

Expert Comment

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

Expert Comment

by:TommySzalapski
ID: 34177768
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
ID: 34178950
Where is the Excel List?

In the Worksheet or as Tommy Suggested in a Combobox?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 45

Expert Comment

by:patrickab
ID: 34179519
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
ID: 34179995
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
ID: 34180685
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
ID: 34180923
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
ID: 34181448
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
ID: 34181576
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
ID: 34181672
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
ID: 34181676
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
ID: 34181712
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
ID: 34182235
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
ID: 34182269
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34182627
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
ID: 34182705
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
ID: 34185246
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
ID: 34185335
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
ID: 34186749
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
ID: 34187067
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
ID: 34187189
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
ID: 34190365
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
ID: 34190540
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
ID: 34190814
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
ID: 34190851
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
ID: 34191962
Tks - :)) - Patrick
0
 
LVL 2

Author Closing Comment

by:John Fistere
ID: 34220928
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
ID: 34221433
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

734 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