If the user inputs lower case y/n answer How do I convert automat.

J

jhg1226

In Excel I have a column which asks for a Y or N response. If the user
inputs y or n in lower case Can I have Excel convert it to Upper Case?
 
B

Bernard Liengme

Use Data Validation to force user to input uppercase
But, Excel is not case sensitive so =IF(A1="Y", Dothis, Dothat) will give
DoThis for A1 = Y or y
best wishes
 
J

Jason Morin

Right-click the sheet tab, go to View Code, and insert
the code below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Column = 1 Then '1 = col. A, 2 = col. B, etc.
If .Count = 1 Then
If .Value = "y" Or .Value = "n" And _
.Value = LCase(.Value) Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End If
End With
End Sub

----
Press ALT+Q to return to XL. The code is set to evaluate
entries in col. A (1). Change if necessary.

HTH
Jason
Atlanta, GA
 
Top