Can I enter a "Y" in a cell and have it return a "YES"? How?

[rbbbbeee]

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?

 

[Greg Wilson]

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

 

[Ken Johnson]

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

 

[Greg Wilson]

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