Yes or No

Experts,
I've looked at data validation and it is not getting me what I need.

In my spreadsheet from H12:H22 the user can only type in a yes or a no. Anything else is not allowed. The yes and no needs to be capitlaized regardless of how the user enters it. I've looked at the Workbook_Change event.
Frank FreeseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nutschConnect With a Mentor Commented:
Hi, this code should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range

Application.EnableEvents = False
If Not Intersect(Target, Range("H12:H22")) Is Nothing Then
    For Each cl In Intersect(Target, Range("H12:H22")).Cells
        If UCase(cl) <> "YES" And UCase(cl) <> "NO" Then
            cl.ClearContents
        Else
            cl = UCase(cl)
        End If
    Next
End If
Application.EnableEvents = True
End Sub

Open in new window


Thomas
0
 
sdwalkerConnect With a Mentor Commented:
Data Validation DID work for me.  Select to Allow a list and for the Source, type in "YES,NO" (without the quotes).

It restricts the answers to capital YES and NO.
0
 
FernandoFernandesCommented:
sdwalker's suggestion is indeed the cleanest, and works as you want...
but as far as I understand, the user is somehow supposed to be able to enter "yes", butyou want this to be converted to "YES", right ?
in this case, nutsch solution is the most appropriate... but it will put VBA in your file... means, you get what you want, but as a "gift" you'll always have to enable macros...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.