Cell Validation

  • Thread starter Thread starter Tracey
  • Start date Start date
T

Tracey

I am working on a spreadsheet, one of the columns is to
enter a product name. The products have to be specific as
they are seleced from a VLookup table.

I have used select from list on the validation, and
specified the range to choose from, but everytime I click
on the down arrow to select the product I am at the bottom
of the list.

Does anyone know how to set this so I could enter a letter
ie. 'b' and go to the b's on the list of 't' to go to the
t's on the list to save scrolling up and down.

Any suggestions would be appreciated
 
When you open the Data Validation dropdown list, it takes you to the
first item that matches the cell contents. If the cell is blank, and
there are blank cells in your data validation source list, the first of
these will be selected in the dropdown list.

To prevent this, remove the blank cells from the data validation source
list, or have a default entry in the cell, and the list will open to
that item.

Unfortunately, the Data Validation list doesn't have an option to go to
a list item based on the letters that you start typing in the cell. You
can type the complete entry, or use the mouse or keyboard navigation
keys to move through the list.
 
Tracey
There is a way to get what you want but, as Debra says, Data Validation
alone won't do it for you.
You could use a Worksheet_Change event macro to reset your Data
Validation list to what you want. This may or may not suit your fancy.
Here is how it would work.
Say you want all the items that start with a "T" to come up when you
click the down arrow of the Data Validation cell. You would type a "T" or a
"t" in the cell and press Enter. This action would trigger a macro to reset
the Data Validation list to only those items in the main list that start
with a "T". You would then click on the down arrow of the cell and there is
your "T" list. As you see, this is a 2-step process. Post back or contact
me direct (remove "nospam" from my email address) if you want to go this
route and you need help with it. HTH Otto
 
Thanks for your reply - this sounds like it would do what
I am looking for - however it sounds quite complicated, I
am only used to doing simple macros. Any help you could
give me with this would be much appreciated.

Thanks

Tracey
 
Tracey
Here is the code (macros) that you need. Note that the first macro is a
sheet event macro and must be placed in the sheet module for the pertinent
sheet. The second macro is called by the first and must be placed in a
regular module.
I assumed your data is in Column A and the Data Validation cell is B1.
Note that the Data Validation must be setup to allow a manual entry (a "t"
for instance).
I am sending you, via email, a small file that has all this code in the
proper places as well as the Data Validation cell properly set up. Please
post back or contact me direct (remove "nospam" in my email address) if you
need any help with this. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$B$1" Then Exit Sub
If Target = "" Then Exit Sub
Call SetAlphaList(Target.Value)
End Sub

Sub SetAlphaList(Letter As String)
Dim WholeList As Range
Dim FirstCell As Range
Dim LastCell As Range
Set WholeList = Range("A1", Range("A" & Rows.Count).End(xlUp))
If Not (WholeList.Find(What:=Letter, LookAt:=xlWhole) Is Nothing) _
Then Exit Sub
Letter = Letter & "*"
Set FirstCell = WholeList.Find(What:=Letter, _
After:=WholeList(WholeList.Count), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
Set LastCell = WholeList.Find(What:=Letter, _
After:=WholeList(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
MatchCase:=False)
Range(FirstCell, LastCell).Name = "AlphaList"
End Sub
 
Back
Top