Letter recognition in a Validated drop-down

[Brad E.]

When I use Data Validation to limit entries in a cell (lets say I am looking
for a day of the week), how can I let the user simply enter the letter M to
get Monday to automatically pop into the cell, and then have to Tab or Enter
to go to the next cell?

Also, how would Tuesday and Thursday be treated? 2 T's to get to Thursday
or TH to get Thursday?

Thank you,

 

[Rick Rothstein]

I don't think you can do what you want with a Data Validation list... as far
as I know, once Edit mode has been entered, VB is inactive. The only
keyboard alternative I can offer you is to press the Alt+{DownArrow} to open
the list box associated with the Data Validation list and then arrow down to
the desired selection and then press the Enter key to confirm it (or,
instead of the Enter key, you can press Alt+{UpArrow} to simply close the
list box back up if you don't want to move to another cell for some reason).

 

[Roger Govier]

Hi
First apply DV to the cells required setting List as
M,T,W,Th,F,S,Su,m,t,w,th,f,s,su,SU,TH
This will ensure the user can only enter the correct values (unless they
copy and paste values into the cell)

On another sheet, I used Sheet2, set up a Lookup range as follows in cells
A1:B7
F Friday
M Monday
S Saturday
Su Sunday
T Tuesday
Th Thursday
W Wednesday

Then add the following code to your worksheet to convert the user entry to a
true weekday.
Change the range "myrange" to match the range of your validated cells.
Change the range "wdays" to match wherever you have put the lookup table as
described above

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myrange As Range, wdays As Range
Set wdays = ThisWorkbook.Sheets("Sheet2").Range("A1:B7") '<== change to suit
Set myrange = Range("A1:A10") '<== change to suit

If Intersect(Target, myrange) Is Nothing Then Exit Sub
If Target.Value <> "" Then
Application.EnableEvents = False
Target = WorksheetFunction.Lookup(Target, wdays)
Application.EnableEvents = True
End If
End Sub

Copy the Code above
Right click Sheet tab > View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

This is event code which will be triggered automatically.

 

[Bassman62]

You can achieve this without code by creating the list of weekdays in the
cells immediately above entry cell. (The rows containing the list will be
hidden)
Set data validation for the entry cell to allow a List and set the source as
the list of weekdays.
Hide the rows containing the list.
In Options, "Enable AutoComplete for cell values" must be checked.
With AutoComplete enabled, the user will only have to enter the first one or
two characters of the day.
With Data Validation set, the entry will be limited to the list.

A1= Sunday
A2= Monday
A3= Tuesday
A4= Wednesday
A5= Thursday
A6= Friday
A7= Saturday
A8: [Entry Cell] (Data Validation= List of $A$1:$A$7)

Hope this helps.
Dave

 

[Roger Govier]

Very neat, Dave

--
Regards
Roger Govier

You can achieve this without code by creating the list of weekdays in the
cells immediately above entry cell. (The rows containing the list will be
hidden)
Set data validation for the entry cell to allow a List and set the source
as
the list of weekdays.
Hide the rows containing the list.
In Options, "Enable AutoComplete for cell values" must be checked.
With AutoComplete enabled, the user will only have to enter the first one
or
two characters of the day.
With Data Validation set, the entry will be limited to the list.

A1= Sunday
A2= Monday
A3= Tuesday
A4= Wednesday
A5= Thursday
A6= Friday
A7= Saturday
A8: [Entry Cell] (Data Validation= List of $A$1:$A$7)

Hope this helps.
Dave

When I use Data Validation to limit entries in a cell (lets say I am
looking
for a day of the week), how can I let the user simply enter the letter M
to
get Monday to automatically pop into the cell, and then have to Tab or
Enter
to go to the next cell?

Also, how would Tuesday and Thursday be treated? 2 T's to get to
Thursday
or TH to get Thursday?

Thank you,