Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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

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
John Fistere
Asked:
John Fistere
  • 10
  • 10
  • 7
  • +1
2 Solutions
 
TommySzalapskiCommented:
If you use control dropdowns instead of data validation this will work.
0
 
TommySzalapskiCommented:
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
 
SiddharthRoutCommented:
Where is the Excel List?

In the Worksheet or as Tommy Suggested in a Combobox?
0
Independent Software Vendors: 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!

 
patrickabCommented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
patrickabCommented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
patrickabCommented:
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
 
patrickabCommented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
John FistereEngineer, retiredAuthor Commented:
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
 
patrickabCommented:
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
 
patrickabCommented:
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
 
patrickabCommented:
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
 
TommySzalapskiCommented:
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
 
TommySzalapskiCommented:
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
 
patrickabCommented:
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
 
TommySzalapskiCommented:
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
 
patrickabCommented:
Tks - :)) - Patrick
0
 
John FistereEngineer, retiredAuthor Commented:
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
 
patrickabCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 10
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now