Announcement

Collapse
No announcement yet.

Data Validation List Accpet only Uppercase

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Data Validation List Accpet only Uppercase

    I would like a data validation list to only accept an uppercase Y or N from the dropdown in a cell, but users can type a lowercase Ys and Ns and they are accepted. I would like the cell to only accept the uppercase Ys and Ns from the dropdown. Does this require code or is there a simpler way? Thanks!

  • #2
    Re: Data Validation List Accpet only Uppercase

    I've just tried this. If you make the Source box contents Y,N then set an error message, entering lower-case y or n will give you an error message.
    .

    Comment


    • #3
      Re: Data Validation List Accpet only Uppercase

      Thanks. I tried this, but it still says accepts a lowercase N and Y. It must require VBA code.

      Originally posted by ByTheCringe2
      I've just tried this. If you make the Source box contents Y,N then set an error message, entering lower-case y or n will give you an error message.
      Last edited by chris46521; July 20th, 2006, 05:31.

      Comment


      • #4
        Re: Data Validation List Accpet only Uppercase

        Here is the code for forcing uppercase in a range of cells:

        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
        ''''''''''''''''''''''''''''''''''''''''''''
        'Forces text to UPPER case for the range A1:B20
        ''''''''''''''''''''''''''''''''''''''''''''
        If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
        
            On Error Resume Next
            If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
            	Application.EnableEvents = False
                Target = UCase(Target)
                Application.EnableEvents = True
            End If
            On Error GoTo 0
        
        End Sub
        Last edited by Dave Hawley; July 20th, 2006, 14:46.

        Comment


        • #5
          Re: Data Validation List Accpet only Uppercase

          chris46521, please use code tags for any code as you agreed

          NO VBA is needed. Simply use Formula is: =OR(exact(A1)="Y",exact(A1)="N")

          Comment


          • #6
            Re: Data Validation List Accpet only Uppercase

            Originally posted by chris46521
            Thanks. I tried this, but it still says accepts a lowercase N and Y. It must require VBA code.
            Please look at the attached sheet. Unless there is some difference between Excel 2000 and Excel 2003, you must have something wrong - it definitely does NOT accept lower case in the attachment. Maybe some option needs switching.
            Attached Files
            .

            Comment

            Working...
            X