Forcing CAP and Validate
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Forcing CAP and Validate

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I would like to force user data to CAPS on enter as well as validate to limit A, B, C, and D.

    I can validate with drop down; but when drop dn list appears, user can enter lower case of above and enter (doesn't force Upper).

    I tried using =EXACT(A1,UPPER(A1)) with Data Validation Custom which forces entry to Upper -but unable to limit to A, B, C, and D.

    Ideas? TIA

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    This will work, but there may be another way that I do not know of...

    In Sheet Change Event
    For Cell a1
    Edit to suit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = "$A$1" Then
    Target.Value = UCase(Target.Value)
    Select Case Target.Value
    Case "A", "B", "C", "D"
    Application.EnableEvents = True
    Exit Sub
    Case Else
    MsgBox "Invalid - Only A,B,C,D accepted"
    Target.ClearContents
    Target.Select
    Application.EnableEvents = True
    Exit Sub
    End Select
    End If
    End Sub

    Good Day!
    Tom

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,809
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-04 01:36, Fun_Geek wrote:
    I would like to force user data to CAPS on enter as well as validate to limit A, B, C, and D.

    I can validate with drop down; but when drop dn list appears, user can enter lower case of above and enter (doesn't force Upper).

    I tried using =EXACT(A1,UPPER(A1)) with Data Validation Custom which forces entry to Upper -but unable to limit to A, B, C, and D.

    Ideas? TIA
    Try:

    =AND(EXACT(A1,UPPER(A1)),CODE(A1)>=65,CODE(A1)<=68)

    Aladin

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mike


    You maybe over complicating a simple issue. In the "Source" box of the Valdation just type:

    A,B,C,D

    They will not be able to enter lower case!

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks! Solution has worked...you guys are the greatest!!!

    Mike

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Mike

    Just curiuos which method you used?



  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    Used your methods as it sounded (and it was) the easiest. I was looking at some VBA work in a couple of other boards and here, but KISS always wins for me!

    Thanks again...I have bookmarked and will be checking back often.

    Mike

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi Mike

    I couldn't agree more!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com