Suppress event

[tiredofit]

Sheet1 contains two columns of data: Name and Quantity.

I want the user to enter a value in the Name column before a value is entered into the Quantity column.

Quantity contains Data Validation. ensuring only a whole number greater than 0 can be accepted.

This is my code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then

        If Me.Cells(Target.Row, 1).Value = vbNullString Then
    
            MsgBox "Enter name first"
        
            Application.EnableEvents = False
        
            Application.Undo
        
            Application.EnableEvents = True
        
        End If

    End If

End Sub

If the user enters a numerical value in column 2 but column 1 does not contain a name, the message box shows up, as expected.

The problem is if the user enters a non-numerical value in column 2 (when again column 1 does not contain a name), DV kicks in, showing invalid value entered.

If I click Cancel, the message box shows up twice.

How can I amend it so the message box only shows up once if the user enters a non-numerical value into column 2?

Thanks

 

[Fluff]

Why not just do it all with DV using

=AND(A2<>"",AND(B2>0,INT(B2)=B2))

and uncheck "Ignore blank"

 

[tiredofit]

Why not just do it all with DV using

=AND(A2<>"",AND(B2>0,INT(B2)=B2))

and uncheck "Ignore blank"

Thanks, seems easier with just DV but was puzzled why the event occurred twice.

 

[Fluff]

It's because you changed the cell twice, once to enter an invalid value & then removed it.

 

[tiredofit]

Thanks for the explanation.

 

[Fluff]

You're welcome & thanks for the feedback.