• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

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.
0
Frank Freese
Asked:
Frank Freese
2 Solutions
 
sdwalkerCommented:
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
 
nutschCommented:
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
 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now