Data Validation

[Todd]

I have been using data Validation to alert users when data
is input incorrectly. However, rows need to be inserted
into the middle of the spread sheet and formulas pasted in
those rows. So my warnings are getting knocked off.

Can I do the same with a macro? Running for the whole
column and all the time?

If data entered in Column H: A message box saying "Must
Use Valid Date"

If Cell is used in D2 and not its precedent cell in C2 A
message Box saying "Must Enter Depreciation Period"


Thanks, Todd

 

[Dave Peterson]

How about a worksheet change event, right click on that worksheet tab that
should have this behavior. Select view code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim DateRng As Range
Dim DepRng As Range
Dim myCell As Range

On Error Resume Next
Set DateRng = Intersect(Target, Range("H:H"))
Set DepRng = Intersect(Target, Range("d:d"))
On Error GoTo 0

On Error GoTo errHandler:

If DateRng Is Nothing Then 'didn't touch column H
'do nothing
Else
For Each myCell In DateRng.Cells
Application.EnableEvents = False
If IsEmpty(myCell.Value) _
Or IsDate(myCell.Value) Then
'do nothing
Else
myCell.ClearContents
MsgBox myCell.Address(0, 0) & " Must be a valid date!"
End If
Next myCell
End If

If DepRng Is Nothing Then 'didn't touch column D
'do nothing
Else
For Each myCell In DepRng.Cells
Application.EnableEvents = False
If IsEmpty(myCell.Value) = False _
And IsEmpty(myCell.Offset(0, -1).Value) Then
myCell.ClearContents
MsgBox "Must Enter Depreciation Period in " _
& myCell.Offset(0, -1).Address(0, 0)
End If
Next myCell
End If

errHandler:

Application.EnableEvents = True

End Sub


If the user disables events (or just says no to enable macros), then this won't
work.