Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Data Validation List Accpet only Uppercase

  1. #1
    Join Date
    30th June 2006
    Posts
    47

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    30th June 2006
    Posts
    47

    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.

    Quote 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 at 05:31.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    30th June 2006
    Posts
    47

    Re: Data Validation List Accpet only Uppercase

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

    VB:
    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 at 14:46.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    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")

  6. #6
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Data Validation List Accpet only Uppercase

    Quote 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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: November 1st, 2006, 15:36
  2. Force Choice From List: Data Validation List
    By RutePoint in forum EXCEL HELP
    Replies: 2
    Last Post: October 19th, 2006, 14:37
  3. Data Validation: Formatting Validation List
    By cjbailey in forum EXCEL HELP
    Replies: 2
    Last Post: October 14th, 2003, 22:16
  4. Data Validation: List Box
    By mbeach in forum EXCEL HELP
    Replies: 7
    Last Post: July 30th, 2003, 00:19
  5. Data/Validation list.... SOLVED
    By noeyedeer in forum EXCEL HELP
    Replies: 1
    Last Post: March 26th, 2003, 08:45

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno