Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Letter recognition in a Validated drop-down
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, -- Brad E. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Letter recognition in a Validated drop-down
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). -- Rick (MVP - Excel) "Brad E." wrote in message ... 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, -- Brad E. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Letter recognition in a Validated drop-down
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. -- Regards Roger Govier "Brad E." wrote in message ... 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, -- Brad E. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Letter recognition in a Validated drop-down
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 "Brad E." wrote: 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, -- Brad E. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Letter recognition in a Validated drop-down
Bassman62;243145 Wrote: 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 "Brad E." wrote: 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, -- Brad E. Nice solution Bassman62! -- Leith Ross Sincerely, Leith Ross 'The Code Cage' (http://www.thecodecage.com/) ------------------------------------------------------------------------ Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=67605 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Letter recognition in a Validated drop-down
Very neat, Dave
-- Regards Roger Govier "Bassman62" wrote in message ... 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 "Brad E." wrote: 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, -- Brad E. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
letter recognition in drop down lists | Excel Discussion (Misc queries) | |||
Letter recognition | Excel Programming | |||
Concatenate Results from a Validated Drop Down List | Excel Discussion (Misc queries) | |||
To make sure only the Validated list can be put in the drop down b | Excel Programming | |||
press letter and go 2 entry begin w letter in data validation drop | Excel Programming |