SelectionChange code will ONLY work if cell is Selected AGAIN. Need to work once cell in no longer s

[Corey]

~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Worksheet_SelectionChange(ByVal target As Range)
Const WS_RANGE1 As String =
"C11,C15,F11,F15,I11,I15,L11,L15,O11,O15,R11,R15,U11,U15"
Const WS_RANGE2 As String =
"C8,C12,F8,F12,I8,I12,L8,L12,O8,O12,R8,R12,U8,U12"

Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not Intersect(target, Range(WS_RANGE1)) Is Nothing Then
If target.Value = "" Or target.Offset(-3, 0).Value = "" Then
Exit Sub
End If
If target.Offset(-3, 0).Value > target.Value And _
target.Offset(-2, 0).Value <> Range("V17").Value Then
MsgBox msg, , "...."
target.Offset(0, 0).ClearContents
target.Offset(0, 0).Select
End If
ElseIf Not Intersect(target, Range(WS_RANGE2)) Is Nothing Then
If target.Value = "" Or target.Offset(-2, 0).Value = "" Then
Exit Sub
End If
If target.Value < target.Offset(-2, 0).Value And _
target.Value < Range("V17").Value Then
MsgBox msg, , "...."
target.ClearContents
target.Select
End If
End If
End Sub
~~~~~~~~~~~~~~~~~~~~~~

Code above works well, but i need to re-enter the cell to get it to work.
How can i have it work as i leave the cell in that range instead.
I have other code in the :
Private Sub Worksheet_Change(ByVal target As Excel.Range)

section.

Corey....

 

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static prevcell As Range
Const WS_RANGE1 As String = _
"C11,C15,F11,F15,I11,I15,L11,L15,O11,O15,R11,R15,U11,U15"
Const WS_RANGE2 As String = _
"C8,C12,F8,F12,I8,I12,L8,L12,O8,O12,R8,R12,U8,U12"
Const msg As String = _
"There is an overlap in the Times Entered." & vbNewLine & _
"The next Start Time needs to be equal or greater than the previous
Finish Time."

If Not prevcell Is Nothing Then
If Not Intersect(prevcell, Range(WS_RANGE1)) Is Nothing Then
If prevcell.Value = "" Or prevcell.Offset(-3, 0).Value = "" Then
Exit Sub
End If
If prevcell.Offset(-3, 0).Value > prevcell.Value And _
prevcell.Offset(-2, 0).Value <> Range("V17").Value Then
MsgBox msg, , "...."
prevcell.Offset(0, 0).ClearContents
prevcell.Offset(0, 0).Select
End If
ElseIf Not Intersect(prevcell, Range(WS_RANGE2)) Is Nothing Then
If prevcell.Value = "" Or prevcell.Offset(-2, 0).Value = "" Then
Exit Sub
End If
If prevcell.Value < prevcell.Offset(-2, 0).Value And _
prevcell.Value < Range("V17").Value Then
MsgBox msg, , "...."
prevcell.ClearContents
prevcell.Select
End If
End If
End If
Set prevcell = Target
End Sub




--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Corey]

thanks for the reply Bob.
I replaced the old code with what you had but i do not get anything occuring
now.
Either by entering a value or by re-entering the cell.

COrey....

 

[Bob Phillips]

What I have done is to save the cell every time you select any cell. It also
checks whether that saved cell is one of your listed cells. For instance,
goto C10, nothing happens. Goto C11, nothing happens (yet). Go somewhere
else (anywhere), and the fact that C11 was the previous cell should trigger
the action.

Of course it is possible that nothing is happening because there is
something in the code that means it wouldn't work if the target cells are
not active. I didn't notice this, but I didn't look too hard.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)