Auto Complete Data Validation

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

I'd like to enter characters that would automatically select the proper
value from my data validation list. Currently, I must select the proper
value from the drop down box via mouse, or <alt> down arrow.

Anyway to do this?

Thanks,
Bernie
 
Not using Data|Validation.

But you could use a combobox from the Control Toolbox toolbar. There's a
property that you can set telling it to look for a match.

You could plop that combobox right over the same cell.
rightclick on the combobox and select properties.
look for listfillrange and use the same range as you used for Data|Validation
(or if you typed in the values in the DV dialog, put those values on a different
worksheet (hidden, maybe).

Look for linkedcell and type the address of the cell you're using to hold that
combobox.

Look for Style and change it to fmstyledropdownlist
(prevents users from typing anything they want)

Look for matchentry and try fmmatchentrycomplete (or fmmatchentryfirstletter)
(depends on what you want).

When you're done, use the arrow keys to select that linked cell.
Format|cells|number tab
custom category
In the "Type box", put ;;;
(3 semicolons will hide the value in the cell, but you'll still be able to use
it in your formulas.)
 
Bernie

Cannot be done with default Data Validation.

Debra Dalgleish has a downloadable workbook with a DV ComboBox.

Data Validation Combobox-- Double-click on a cell that contains a data
validation list, and a combobox appears -- font size can be set, more than 8
rows displayed, and autocomplete can be enabled. Macros must be enabled.

http://www.contextures.on.ca/excelfiles.html

Second item on the DV list of workbooks at the above URL

Gord Dibben Excel MVP
 
Thank you Dave and Gord!
I'm going to try both suggestions to see which is best. Debra Dalgleish's
web site is very nice, so I'll probably start there.
Thanks again,
Bernie
 
Dear Gord Dibben
I visited ur site and really liked the partlocDB example. Will u please
guide me how can i create a similar form. ( I am very new to VB)

thanks in advance.
Nadeem
 
Back
Top