Validate data but then change the entry to upper case
Assuming column A for entry
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
If Target.Value = "w" Or Target.Value = "l" Then
Target.Formula = UCase(Target.Formula)
Else: Target.Value = "try again"
End If
ErrHandler:
Application.EnableEvents = True
End Sub
Gord Dibben MS Excel MVP
On Sat, 20 Mar 2010 08:37:18 -0700 (PDT), Ephraim
wrote:
On Mar 20, 11:06*am, Teethless mama
wrote:
DV
Allow: List
Source: W,L
Make sure In-cell dropdown is checked
"Ephraim" wrote:
Hi,
Is it possible to validate data to allow only W, w, L, l and then
convert the lower case entry to upper case?
I don't want the user to have to keep pressing shift or caps lock
while entering data in these cells.
Ephraim
.- Hide quoted text -
- Show quoted text -
Unfortunately the cells are so close together that using a list with
in-cell dropdown covers the cell next to it which slows data entry.
I was hoping to find a solution that would allow the user to input
either W,w,L or l and then convert the lower case to upper case
automatically.
W or w represents a Win and L or l represents a Loss. Because the
lower case l may be confused with the number 1 I wish to keep
everything in upper case. A win is worth one point and a loss get's
zero points.
|