Auto Complete Data Validation

[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

 

[Dave Peterson]

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.)

 

[Gord Dibben]

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

 

[bw]

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

 

[Nadeem Shafiullah]

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

 

[Gord Dibben]

Nadeem

The site belongs to Debra Dalgleish and the file in question is her creation.

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

For basics on User Forms see

http://msdn.microsoft.com/library/d...ffpro01/html/IntroductiontoUserFormsPartI.asp

Also John Walkenbach has a site with various User Form examples and
Tips.......downloads included.

Gord Dibben Excel MVP

 

[Debra Dalgleish]

I've added a page with instructions for creating a UserForm, based on
the Parts Inventory example:

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

 

[Gord Dibben]

Good stuff Debra.

One more link for the needy.

Gord