Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Ude validation as an Input Mask? (Excel 2000 >)

    Hi,
    I have been playing around with Data Validation and pushing it to its limits (I assume)!
    I would like to know if it is possible using it like an Access Input Mask?

    If I have in Column "A", values like XLS2003, WRD2002, PPT1998, ADB2002, etc, could I use validation to warn of incorrect data format if it does not have 3 text characters on the left and 4 numerical characters on the right?

    I have noticed that the numbers are seen as text because it is a text string, but is there a way around this? If there is a solution, it will be very useful to me! Thanx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ude validation as an Input Mask? (Excel 2000 >)

    A bit clunky: create the following custom function in a module:

    Function Validate3Plus4(sText As Variant) As Boolean
    Dim i As Integer
    Dim c As Integer
    If Len(sText) <> 7 Then
    Exit Function
    End If
    For i = 1 To 3
    c = Asc(UCase(Mid(sText, i)))
    If c < 65 Or c > 90 Then
    Exit Function
    End If
    Next i
    For i = 4 To 7
    c = Asc(Mid(sText, i))
    If c < 48 Or c > 57 Then
    Exit Function
    End If
    Next i
    Validate3Plus4 = True
    End Function

    We cannot use custom functions directly in the Validation dialog, so we use a workaround.
    Say that your data start in A1.
    In B1, enter the formula =Validate3Plus4(A1) and fill down as far as needed.
    Select the cells in column A, starting with A1.
    Select Data | Validation...
    Select 'Custom' in the Allow dropdown list.
    Enter =B1 in the Formula box.
    Keep the 'Ignore blank' box checked.
    Activate the 'Error Alert' tab.
    Make sure the 'Stop' style is selected.
    Enter a title and text for the error message.
    Click OK.
    Column B can be hidden, if desired.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ude validation as an Input Mask? (Excel 2000 >)

    Wow...I'll try this out and see if it works.
    You have shown me something new here Hans. It seems that there is almost no limit then to validation, because if a person can create a custom function and then refer to it in the validation feature, you could design any custom function you like and incorporate it into validation!!!
    Does this also apply to Conditional Formatting? If I design a custom function to apply formatting, could I also refer to the function in the conditional format feature? If yes then there is almost no limit to this feature too!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Ude validation as an Input Mask? (Excel 2000 >)

    It works the same for conditional formatting: you can put a formula with a user-defined function in a cell, select Formula Is in the conditional formatting dialog and refer to the cell with the formula.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ude validation as an Input Mask? (Excel 2000 >)

    Assume column A house your datas

    Select the cells in column A, starting with A1.

    Choose Data | Validation...

    Select the Settings tab

    In the Allow dropdown list., Select 'Custom'

    In the Formula box, enter

    =SUMPRODUCT(--(ABS(CODE(MID(A1,ROW($1:$3),1))-77.5)<13))=3*ISNUMBER(--RIGHT(A1,4))

    Click OK


    Hope that helps
    Bosco

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Ude validation as an Input Mask? (Excel 2000 >)

    Thanx 4 your input and time Bosco. It's quite some formula that you've put together. I have stored it on file, but taken Hans's advice and developed it to my needs as it allows for more flexibility.
    Regards,
    Rudi

Posting Permissions

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