MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Auto Enter into a cell

Posted by Barrie Bowdler on October 04, 2001 4:22 AM

I want to type the letter "Y" or "N" into a cell and have the cell automatically display "Yes" for the "Y" or "No" for the "N". How can I do this please, please!

Posted by Robb on October 04, 2001 4:42 AM


Since you want to type into the cell and have it show as something
different, I think you will need to use code.

Try this in the Worksheet code page:

- Right click on the Sheet tab
- Select "View Code"
- Copy/paste this code into the code window that will appear
- Amend the Sheet name (currently Sheet3) and
range (shown as .Cells(5,3) - which is C5)
- If you type Y or y or N or n in that cell, Yes or No will be shown.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet3")
If Not Application.Intersect(Target, .Cells(5, 3)) Is Nothing Then
If Target = "Y" Or Target = "y" Then Target = "Yes"
If Target = "N" Or Target = "n" Then Target = "No"
End If
End With
End Sub

Any help?


Posted by Barrie Davidson on October 04, 2001 7:07 AM

You could also set this up in Tools|AutoCorrect.


PS - that's a great first name you've got there!!Barrie Davidson