VBA Message Box when neighboring cells have values

[KP_SoCal]

I’m looking for a way to write some VBA code the will create a message any time a numeric value is entered into two cells adjacent to each other. In other works, the range for the first column would be J2:J100 and the range for the second column would be K2:K100. So if the end user tries to entire a value in K7 when a value already exists in J7, I want a message to fire to saying something to the effect “Dual input values not allowed!” In a simple formula might look something like this: =IF((AND(J8>0,K8>0)),"Not Allowed","Allowed"), but obviously I need something a little more complex. There is a posting on this link that gets me within ballpark, but I’m still stumped as to how implement in the context of my described scenario (see image illustration).

Also, I do not want the inputted value to not be accepted. If I were doing this on an Access Form Control I would write some code like Me.Textbox.Undo after the MsgBox fires. I’m not sure how to do this in excel. So if a value exists in K3 and the end user tried to input a value in J3, the MsgBox would fire and the value in J3 would be reset to null and vice versa if a value already exists in J3 and user tries to input in K3.

 

[lenze]

Why not just use Data Validation?
Custom, Formula

=COUNT($J2:$K2)<2

Customize your message on the Alert Tab

lenze

 

[KP_SoCal]

The Data Validation method would be perfect accept for the message box that it prompts you with (see illustration).

Instead of [Retry] [Cancel] [Help], I would only like to display the [OK] button and perform the same action as the [CANCEL] button. I assume this would have to be done through VBA? Any suggestions on how to go about that? Thanks!

 

[lenze]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$J$2:$K$100")) Is Nothing Then Exit Sub
Select Case Target.Column
Case 10
If Cells(Target.Row, "K") <> "" Then
    MsgBox "Not Allowed"
    Application.EnableEvents = Flase
    Application.Undo
    Application.EnableEvents = True
Else: MsgBox "Allowed"
End If
Case 11
If Cells(Target.Row, "J") <> "" Then
    MsgBox "Not Allowed"
    Application.EnableEvents = Flase
    Application.Undo
    Application.EnableEvents = True
Else: MsgBox "Allowed"
End If
Case Else:
End Select
End Sub

lenze

 

[KP_SoCal]

Perfect Lenze! Thanks for your help and have a nice Thanksgiving!!!

 

[lenze]

I hope you corrected mu typo
Application.EnableEvents = Flase
lenze

 

[KP_SoCal]

I hope you corrected mu typo
Application.EnableEvents = Flase
lenze

Yes sir I did, thanks for checking! =)