vba excel data validation - how to clear entry?

[plhuff]

I get the input and error title I want on invalid Excel dates, but clicking 'Ok' still leaves the invalid entry. Clicking 'Cancel' clears it.
Is my syntax wrong? Can I refer to the cell in error and clearcontents on it?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim lValue As Boolean
Dim lRow As Long, lDVTYpe As Long
Dim sTemp As Shape
Dim ws As Worksheet
Dim rCell As Range, c As Range
Dim sMyString As String
Application.EnableEvents = False
Set ws = ActiveSheet
lRow = Cells(Rows.Count, "A").End(xlUp).Row
'We set our range variable


Set rCell = ActiveSheet.Range("AZ2:AZ" & lRow)


On Error Resume Next
lDVTYpe = 4
lDVTYpe = Target.Validation.Type
On Error GoTo errHandler


With Range("AZ2:AZ" & lRow).Validation
.Modify Type:=xlValidateDate, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="01/01/1900", Formula2:="12/31/2600"
.InputTitle = "Input"
.ErrorTitle = "Enter "
.InputMessage = " mm/dd/yyyy"
.ErrorMessage = " mm/dd/yyyy"
.IgnoreBlank = True
 MsgBox ("At1 " & rCell.Address & " " & rCell.Value)
End With
errHandler:
  MsgBox ("Error Handler")
  MsgBox (Target.Address)
  Application.EnableEvents = True
  Exit Sub
End Sub
/CODE

phuff

 

[mrmmickle1]

In order to clear the active cell contents you can use:

Target.ClearContents

 

[plhuff]

In order to clear the active cell contents you can use:

Target.ClearContents

Thanks! Will that clear only the error cell's contents? (which is what I need to do)

 

[mrmmickle1]

This will clear the cell(s) that you have selected.

Target.ClearContents

This will clear cells(s) that have formulas that result in errors:

[COLOR=#0000ff]    On Error Resume Next
[/COLOR][COLOR=#008000]    '16 signifies the constant for xlErrors[/COLOR]
    Target.SpecialCells(xlCellTypeFormulas, 16).ClearContents
[COLOR=#0000ff]    On Error GoTo 0[/COLOR]