Custom Format?

[paulstan]

I need a column to accept one of the following entries only:

• be left blank
• date (in dd/mm/yyyy) format
• or the word 'Validated'

If anything else is input, then an error message is displayed. Am I able to do this through a custom format, or will it require VBA?

Regards

Paul S

 

[Rick Rothstein]

@Paul,

Okay, I see the problem. We need to use two event procedures to get around this problem. Replace the Change event code procedure you are now using with the following two event code procedures...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range, CellText As String
    If Not Intersect(Target, Columns("A")) Is Nothing Then
    For Each Cell In Target
        CellText = UCase(Cell.Text)
        If Len(CellText) = 0 Then Exit Sub
        If CellText = "NO CLAIM" Then
            Application.EnableEvents = False
            Cell.Value = "Validated"
            Application.EnableEvents = True
            Exit Sub
        ElseIf IsDate(CellText) And CellText Like "*[!0-9]*" Then
            Application.EnableEvents = False
            Cell.Value = CDate(CellText)
            Application.EnableEvents = True
            Cell.NumberFormat = "dd/mm/yyyy"
            Exit Sub
        End If
        MsgBox "                 Invalid Entry!" & vbCr & "Please enter one of the following: " & vbCrLf & vbCrLf & "type the text 'no claim'" & vbCr & "date (in dd/mm/yyyy format)" & vbCr & "leave blank"
        Target.Select
    Next
    End If
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        For Each Cell In Target
            Target.NumberFormat = "General"
        Next
    End If
End Sub

Note: I included the change you made from "Validated" to "No Claim" and I also included your current MessageBox message in this code. In addition, I added code to stop the error that gets generated if you try to delete multiple entries.

 

[paulstan]

Hi Rick

Many thanks for that. Unfortunately, when you type in a valid date, leave the cell and then return to it, the date changes to its numerical value; today's date: 15/07/2011 is showing as 40793.

Regards

Paul S

 

[paulstan]

Further explanation!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o>All credit for the coding below goes to Rick Rothstein</o>
<o></o>
I am trying to restrict a column to any of the 3 below which can be entered by the user:

<o></o>

• left blank
• the word ‘Validated’
• or a date entered by the user in the format dd/mm/yyyy

If anything other than the above is entered, an error message appears for the user to try again.
<o></o>
<o>First Code Block</o>
<o></o>
<o>

<o:p> 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range, CellText As String
    If Not Intersect(Target, Columns("A")) Is Nothing Then
    For Each Cell In Target
        CellText = UCase(Cell.Text)
        If Len(CellText) = 0 Then Exit Sub
        If CellText = "VALIDATED" Then
            Application.EnableEvents = False
            Cell.Value = "Validated"
            Application.EnableEvents = True
            Exit Sub
        ElseIf IsDate(CellText) And CellText Like "*[!0-9]*" Then
            Application.EnableEvents = False
            Cell.Value = CDate(CellText)
            Application.EnableEvents = True
            Cell.NumberFormat = "dd/mm/yyyy"
            Exit Sub
        End If
        MsgBox "Invalid Entry!" & vbCr & "Please enter one of the following: " & vbCrLf & vbCrLf & "type the text 'validated'" & vbCr & "date (in dd/mm/yyyy format)" & vbCr & "leave blank"
        Target.Select
    Next
    End If
End Sub
</o:p>

</o><o></o>
When I use the first code block I get the following problem:
<o></o>
A user can type in the word validated, which works correctly. The problem is with dates. A user can enter a correct date, ie <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:date Month="7" Day="18" Year="2011">18/07/2011</st1:date>, which will show correctly; however, the user can over-write this cell with a number, for instance 111 and the date will change to <st1:date Month="4" Day="20" Year="1900">20/04/1900</st1:date>. If they try to over-write with text, then the error message appears.
<o></o>
So, the second code block was added and the following problem now occurs, still with dates. A user can enter a date, ie <st1:date Month="7" Day="18" Year="2011">18/07/2011</st1:date>. Upon leaving the cell everything appears correct; however, when you revisit the cell or roll-over it the date changes to its numerical value, in this case 40742.

Second Code Block

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Cell As Range
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        For Each Cell In Target
            Target.NumberFormat = "General"
        Next
    End If
End Sub

Regards

Paul S