CODEWITHSUNDEEP
vbaexcel
Ken
Ken

Reputation: 19

VBA for data validation

Recently, I found a code on a book for data validation, which is:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = “$A$1” Then
    If Not IsNumeric(Target) Then
    MsgBox “Enter a number in cell A1.”
    Range(“A1”).ClearContents
    Range(“A1”).Activate
    End If
  End If
End Sub

I would like to change it to validate my custom format in column A which is XY & 6 number (XY123456) and modified the code. But the MsgBox will pop up continuously and I cannot close it when the format is wrong. Could someone give me some advice. Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then
    If Left(Target.Value, 2) <> "XY" Or 
       Not IsNumeric(Right(Target.Value,6)) Or 
       Len(Target.Value) <> 8  Then
    MsgBox “Wrong Format”
    Target.ClearContents
    Target.Activate
    End If
  End If
End Sub

Upvotes: 0

Views: 177

Answers (1)

Storax
Storax

Reputation: 12167

Change your code to 

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo EH

    If Target.Column = 1 Then
        If Left(Target.Value, 2) <> "XY" Or Not IsNumeric(Right(Target.Value, 6)) Or Len(Target.Value) <> 8 Then
            Application.EnableEvents = False
            MsgBox "Wrong Format"
            Target.ClearContents
            Target.Activate
        End If
    End If

EH:
    Application.EnableEvents = True

End Sub

You need to turn off events otherwise Target.ClearContents will trigger the event again and again until you run out of stack space. In order to make it a little bit more bullet proof I also added an error handler to make sure the event handler gets turned on again in case of an error.

Upvotes: 1

Related Questions