Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I enter a "Y" in a cell and have it return a "YES"? How?
I am creating a spreadsheet where i have to enter information into a cell.
Instead of having to type the whole word "YES", I would like to be able to enter just the letter "Y" or say the number "1" and have it display the word "YES". This would save me great amounts of time. Is this possible to do? If so, how? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I enter a "Y" in a cell and have it return a "YES"? How?
Assumed is that you want this behavior to occur in column B. Paste to the
worksheet's class module: Right click the worksheet tab Select View Code Paste to the code module. Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Or .Column < 2 Then Exit Sub If LCase(Trim(.Value)) = "y" Or _ LCase(Trim(.Value)) = "yes" Then .Value = "Yes" End If End With End Sub Regards, Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I enter a "Y" in a cell and have it return a "YES"? How?
Hi, If you have the "Allow AutoComplete for cell values" option enabled then you will only have to fully type the first "Yes" and firsst "No". After that "Y" + Enter will give "Yes" and "N" + Enter will give "No". Go Tools|Options|Edit then make sure that option is ticked|OK Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I enter a "Y" in a cell and have it return a "YES"? How?
Sorry. I forgot to disable EnableEvents. Should be:
Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Or .Column < 2 Then Exit Sub If LCase(Trim(.Value)) = "y" Or _ LCase(Trim(.Value)) = "yes" Then Application.EnableEvents = False .Value = "Yes" Application.EnableEvents = True End If End With End Sub "Greg Wilson" wrote: Assumed is that you want this behavior to occur in column B. Paste to the worksheet's class module: Right click the worksheet tab Select View Code Paste to the code module. Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Or .Column < 2 Then Exit Sub If LCase(Trim(.Value)) = "y" Or _ LCase(Trim(.Value)) = "yes" Then .Value = "Yes" End If End With End Sub Regards, Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |