Message box to show

  • Thread starter Thread starter Jock
  • Start date Start date
J

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
 
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
 
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.
 
Back
Top