If Statement
 

I have the following conditional procedure. Everything works well except
when I pressed OK in the Message Box (Time entered does not fall within the
work shift), it goes back to check this line: If Target.Value +
Target.Offset(-9, 2).Value Target.Offset(-1, 0).Value And Target.Value +
Target.Offset(-9, 2).Value Target.Offset(-1, 1). I want it clear the
target.value and exit the sub.
Thanks in advance.


-- Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
Dim j As Integer
Dim ans As Long


If Target.Column = 5 And Target.Row = 27 Then
If Target.Value = "The Contractor did not performed any work
in the field" Then
For i = 1 To 13
For j = 1 To 7
Target.Offset(-i, j) = ""
Next
Next
For i = 1 To 8
For j = 0 To 1
Target.Offset(i, j) = " "
Next
Next
Range("Q5") = " "
Range("Q6") = " "
End If
End If

If Target.Column = 15 And Target.Row = 10 Then
If Target.Value + Target.Offset(-9, 2).Value
Target.Offset(-1, 0).Value And Target.Value + Target.Offset(-9, 2).Value
Target.Offset(-1, 1) Then
Target.Value = Target.Value
Else
MsgBox ("Time entered does not fall within work shift.")
Target.Value = " "
Exit Sub
End If
End If
End Sub
Thanks!

If Statement
 

First, I wouldn't put spaces in the cells.

Target.Offset(i, j) = " "
I'd use:
Target.Offset(i, j) = ""

It makes other formulas easier.

Second, when your code changes a cell, then the worksheet_change event gets
called again (and again and again--until excel gets tired and gives up).

You can stop your changes from invoking this worksheet_change event by:

application.enableevents = false
'something that changes a cell
application.enableevents = true

====
Is there a reason, you're doing
target.value = target.value
(converting a formula to a value????)


Daviv wrote:

I have the following conditional procedure. Everything works well except
when I pressed OK in the Message Box (Time entered does not fall within the
work shift), it goes back to check this line: If Target.Value +
Target.Offset(-9, 2).Value Target.Offset(-1, 0).Value And Target.Value +
Target.Offset(-9, 2).Value Target.Offset(-1, 1). I want it clear the
target.value and exit the sub.
Thanks in advance.

-- Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
Dim j As Integer
Dim ans As Long

If Target.Column = 5 And Target.Row = 27 Then
If Target.Value = "The Contractor did not performed any work
in the field" Then
For i = 1 To 13
For j = 1 To 7
Target.Offset(-i, j) = ""
Next
Next
For i = 1 To 8
For j = 0 To 1
Target.Offset(i, j) = " "
Next
Next
Range("Q5") = " "
Range("Q6") = " "
End If
End If

If Target.Column = 15 And Target.Row = 10 Then
If Target.Value + Target.Offset(-9, 2).Value
Target.Offset(-1, 0).Value And Target.Value + Target.Offset(-9, 2).Value
Target.Offset(-1, 1) Then
Target.Value = Target.Value
Else
MsgBox ("Time entered does not fall within work shift.")
Target.Value = " "
Exit Sub
End If
End If
End Sub
Thanks!

--

Dave Peterson