Message box to show

[Jock]

Hi all,
If the user puts a date in a cell in "K" but "H" on the same row is empty or
has "N" in it, I'd like a message box to appear saying "Not this time" and
for the date to be removed from the cell in "K".

Thanks in advance

 

[Mike H]

Jock,

Right click your sheet tab, view code and paste the code below in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If IsDate(Target) And Target.Offset(, -3) = "" _
Or Target.Offset(, -3) = "N" Then
Application.EnableEvents = False
MsgBox "Not this time"
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Mike

 

[Jock]

Hi Mike,
Thanks but it does not work. Have placed it in Sheet code after another
WorksheetChange event (which still works).

If it can be made to work, could the code be changed to a Critical Stop and
a beep?
Thanks again.