data validation quick find

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a long list of names in the validation database, so scrolling through
the entire list takes a long time.

Is there a way to set up the name find so that as a user starts to type in
the first letters of a name it automatically goes to the right name?

For example if they type "M", it goes to first name starting with "M"; and
if they continue "ME" it goes to first name starting with "ME"?

If basic Excel does not have this feature, do you know of any add-ins or
third party additions that cover this feature?
 
Autocomplete? Recollect Debra Dalgleish once posted:

.. Although data validation doesn't support autocomplete, there's a sample
file here that provides a combobox from which you can select one of the
values from the data validation list. In the combobox, you can enable
autocomplete:

http://www.contextures.com/excelfiles.html

Under 'Data Validation', look for:
DV0043 - Data Validation Combobox With Entry Check
 
Hi Richard,

If you prefer to use validation under Data, you can use this "poor man's"
semi-auto complete.

Atop your list enter A through Z in the column. Sort the list Ascending.
There will be an A at the beginning of all the "A" starting entries and a B
at the start of the "B" entries... etc. on to Z.

Click on the validation cell and enter the letter of choice to access the
list, say you type in an "R". DO NOT hit enter, click the down arrow on the
cell and you will be at the top of the R list. You will still have to
scroll down the R list to your choice. (You can hit Enter when entering the
R, however, you will now have to reselect the validation cell to hit the
down arrow.)

HTH
Regards,
Howard
 
Max,
I'm just starting to get my hands on userforms.
I was able to download the example you suggested.
One question I have, how do you associate a combo box with a specific set of
cells.

For example, in the sample's sheet "ValidationSample" there are only 10 blue
cells that are linked to the list of weekdays. Each cell seems to have a
combo box associated with it that is the exact same size as the cell itself,
and changes size if you change cell height or width. How does this hapen?
 
Howard,
In my case it is not practical to populate the cells atop all the cells in
question.
Thanks anyway.
 
Max,
Debra's web site was very helpful. However, I don't see anywhere where she
associates the combo box with a specific list of cells.

When it comes to adding the combo box, she writes "Click on an empty area of
the worksheet, to add a combo box"

How do you 'put' this combo box in a group of cells? Or do you just put it
in one cell, and then copy/paste?

Richard
 
Thought Debra explains it very clear, with supportive screen graphics? Note
that the combo box is from the control toolbox toolbar, not the forms
toolbar.
"Click on an empty area of
the worksheet, to add a combo box"

How do you 'put' this combo box in a group of cells?
Or do you just put it
in one cell, and then copy/paste?
Click to expand...

Just click on the combo box icon in the control toolbox toolbar, then draw
out a rectangle somewhere on the sheet. It'll float on top of cells.
associates the combo box with a specific list of cells.
Click to expand...

The association is done via the code which needs to be installed on the
sheet* as explained by Debra under the section: "Add the Code". When you
install the code, the combo box drawn earlier will disappear. But it'll
appear when you double-click on the DV list(s) on the sheet.

*remember to replace: "ValidationLists"
in the line below with your actual sheetname:

Set wsList = Sheets("ValidationLists")


---
 
Max,
All working well. Thanks much.
--
Richard


Max said:
Thought Debra explains it very clear, with supportive screen graphics? Note
that the combo box is from the control toolbox toolbar, not the forms
toolbar.


Just click on the combo box icon in the control toolbox toolbar, then draw
out a rectangle somewhere on the sheet. It'll float on top of cells.


The association is done via the code which needs to be installed on the
sheet* as explained by Debra under the section: "Add the Code". When you
install the code, the combo box drawn earlier will disappear. But it'll
appear when you double-click on the DV list(s) on the sheet.

*remember to replace: "ValidationLists"
in the line below with your actual sheetname:

Set wsList = Sheets("ValidationLists")
Click to expand...
 
Back
Top