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

R

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?
 
G

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
 
K

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top