VBA for data validation

Question

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

Storax · Accepted Answer · 2018-09-01 09:02:26Z

1

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.

answered Sep 1, 2018 at 9:02

Storax

12.3k3 gold badges20 silver badges35 bronze badges

Hi Storax, as I am a beginner to VBA, may I know why the original code from book can work properly without "Application.EnableEvents =" however it doesn't work in my code. Thanks
– Ken
Commented Sep 1, 2018 at 9:07
The original is also faulty but it only runs twice because the second time IsNumeric(Target) is true as target is empty.
– Storax
Commented Sep 1, 2018 at 9:10
Good catch on the enableevents but this will error if target is more than a single cell. Also, what happens if a value is cleared?
– user4039065
Commented Sep 1, 2018 at 9:16
And I would like to ask one stupid question that if there is no error, will EH be executed? If not, the Application.EnableEvents will be False all the time?
– Ken
Commented Sep 1, 2018 at 9:16
1

@K Ng: EnableEvents will always run as I did not put an Exit Sub before it. For an introduction to error handling have a look here.
– Storax
Commented Sep 1, 2018 at 9:22

| Show 3 more comments

Collectives™ on Stack Overflow

VBA for data validation

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related