Tip Printed from ExcelTip.com
Prevent entry of invalid values using VBA in Microsoft Excel


Question:
Entries of invalid values in area A1:B6 are to be prevented, in case of an error a respective message box to be displayed.


Answer:
Insert the following code in This Workbook module.








Private Sub Worksheet_Change(ByVal Target As Range)

   Dim var As Variant

   If IsEmpty(Target) Then Exit Sub

   If Intersect(Target, Range("Calculating")) _

      Is Nothing Then Exit Sub

   var = Application.Sum(Range("Calculating"))

   If IsError(var) Then

      Beep

      MsgBox "Please enter the correct value!"

      Target.Value = ""

   End If

End Sub