Mega Code Archive
Validating data entry in Worksheet change event
<![CDATA[
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("A1:C4")
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then
ValidateCode = EntryIsValid(cell)
If ValidateCode = True Then
Exit Sub
Else
Debug.Print "Cell " & cell.Address(False, False) & ":" & ValidateCode
Application.EnableEvents = False
cell.ClearContents
cell.Activate
Application.EnableEvents = True
End If
End If
Next cell
End Sub
Function EntryIsValid(cell) As Variant
If cell = "" Then
EntryIsValid = True
Exit Function
End If
If Not IsNumeric(cell) Then
EntryIsValid = "Non-numeric entry."
Exit Function
End If
If CInt(cell) <> cell Then
EntryIsValid = "Integer required."
Exit Function
End If
If cell < 1 Or cell > 12 Then
EntryIsValid = "Valid values are between 1 and 12."
Exit Function
End If
EntryIsValid = True
End Function
]]>