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]

Maybe you can use this event code to produce the control you want. It allows the user to enter the word "validate" in any letter casing which it then converts to proper letter casing automatically. It also lets the user enter a date in any format they want which it then formats to the form you want. Change the "A" inside the Columns propert call in the first line of code to the column letter you want to have this functionality)...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    If Len(Target.Value) = 0 Then Exit Sub
    If UCase(Target.Value) = "VALIDATED" Then
      Application.EnableEvents = False
      Target.Value = "Validated"
      Application.EnableEvents = True
      Exit Sub
    ElseIf IsDate(Target.Value) Then
      Target.Value = CDate(Target.Value)
      Target.NumberFormat = "dd/mm/yyyy"
      Exit Sub
    End If
    MsgBox "Invalid entry!!"
    Target.Select
  End If
End Sub

In case you are not familiar with installing event code, right click the name tab at the bottom of the worksheet you want to have this functionality and select View Code, then copy/paste the above code into the code window that opened up. That's it... your column now has the functionality described above.

 

[paulstan]

Hi Rick

Many thanks for your reply to this thread.

I have run the code and the text validation works without a problem; however, I do encounter a problem when entering a date. Even entering a valid date in dd/mm/yyyy format brings up the error:

Run-time error '-2147417848 (80010108)':Method 'NumberFormat' of object 'Range' failed

This then stops Excel from working.

For information, I'm using Excel 2007

Regards

Paul S

ps. Debug goes to line:

Target.NumberFormat = "dd/mm/yyyy"

 

[Rick Rothstein]

I have run the code and the text validation works without a problem; however, I do encounter a problem when entering a date. Even entering a valid date in dd/mm/yyyy format brings up the error:

Run-time error '-2147417848 (80010108)':Method 'NumberFormat' of object 'Range' failed

This then stops Excel from working.

For information, I'm using Excel 2007[/CODE]

Hmm, XL2007 is more sensitive to the reentry of the date than XL2003 is (I developed the event code in XL2003 where is worked without a hitch). Here is modified code that should work correctly on XL2007 as well as XL2003...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("A")) Is Nothing Then
    If Len(Target.Value) = 0 Then Exit Sub
    If UCase(Target.Value) = "VALIDATED" Then
      Application.EnableEvents = False
      Target.Value = "Validated"
      Application.EnableEvents = True
      Exit Sub
    ElseIf IsDate(Target.Value) Then
      Application.EnableEvents = False
      Target.Value = CDate(Target.Value)
      Application.EnableEvents = True
      Target.NumberFormat = "dd/mm/yyyy"
      Exit Sub
    End If
    MsgBox "Invalid entry!!"
    Target.Select
  End If
End Sub

 

[paulstan]

Hi Rick

That works a treat on 2007 (at home).

I left work too late to try it out there, but your original code would have worked without any problems as they have 2003. Will the 2007 code also work on 2003, or best sticking to your original? (Sorry about that - I should have specified Excel version in the original post).

Lastly, I may need the code to only run from, say Col A, Row 3 down to Col A, Row 500, for example. Is this easy enough to change?

Regards

Paul S

 

[Scott Huish]

Perhaps Data Validation.
I used Column A in this example.

Allow Custom:

=OR(CELL("format",$A1)="D4",$A1="Validated")

 

[Rick Rothstein]

Will the 2007 code also work on 2003?

Yes

Lastly, I may need the code to only run from, say Col A, Row 3 down to Col A, Row 500, for example. Is this easy enough to change?

Change this line of code...

If Not Intersect(Target, Columns("A")) Is Nothing Then

to this...

If Not Intersect(Target, Range("A3:A500")) Is Nothing Then

 

[paulstan]

Rick

That worked a treat - many, many thanks


HotPepper

Thanks for your input as well (I know you've helped me out in the past on a few occasions)

Regards both

Paul S

 

[paulstan]

Rick

Have tried it out and it works on 2003.

After more testing though I have come across a small error, which concerns dates.

I can enter a valid date ie, 15/07/2011 (ddmmyyyy) but if I return to the cell and overtype it say, with 111 or any number combination the date entry reverts to 20/04/1900.

Current code attached. I'm running with the 2007 version as this seems to work much quicker (even on 2003). With my limited knowledge, I have altered a few things, but I don't think this has affected anything.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B16")) Is Nothing Then
On Error Resume Next
 
If Len(Target.Value) = 0 Then Exit Sub
If UCase(Target.Value) = "NO CLAIM" Then
    Application.EnableEvents = False
    Target.Value = "No Claim"
    Application.EnableEvents = True
Exit Sub
ElseIf IsDate(Target.Value) Then
    Application.EnableEvents = False
    Target.Value = CDate(Target.Value)
    Application.EnableEvents = True
    Target.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.ClearContents
    Target.Select
   'Selection.ClearContents
 
End If
End Sub

Regards

Paul S

 

[paulstan]

Adendum to my previous post

The dates don't just revert to 20/04/1990. Typing 5000 will return a date of 08/09/1913.

As mentioned, this only happens when a previous correct date was entered and then the cell over-typed.

Regards

Paul S