Programatic Data Validation

J

Jdibble

Any exmamples of how to use Programatic Data Validation? For example I'd like to restrict the users from entering letters in a date column. I'm assuming the code is rather short, just haven't been able to figure it out yet. Thanks
 
H

Harald Staff

Hi

Rightclick sheet tab, choose "view code", paste this in :

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Column = 1 Then 'A column
If .Value <> "" Then
If IsDate(.Value) = False Then _
.Value = ""
End If
End If
End With
End Sub

HTH. Best wishes Harald

Jdibble said:
Any exmamples of how to use Programatic Data Validation? For example I'd
Click to expand...
like to restrict the users from entering letters in a date column. I'm
assuming the code is rather short, just haven't been able to figure it out
yet. Thanks
 
T

Tom Ogilvy

Just some added info
When using the change event to change a value in the cell that triggered the
event, it is advisabel to disable events to prevent recursive calling

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
With Target(1)
If .Column = 1 Then 'A column
If .Value <> "" Then
If IsDate(.Value) = False Then _
Application.EnableEvents = False
.Value = ""
End If
End If
End With
Errhandler:
Application.EnableEvents = True
End Sub
 
Top