Using VBA to change one cell if another ones does..?

I am working on a scheduler that will, if a shift is entered in Column B, will check to see if there is an employee in Column A (same row).

If there is no employee, the shift will be deleted. (Can't have a shift without an employee to go with it!)

My code performs well until it reaches a situation where, yes, there is an employee entry in Col A, then it fails to correct any future situation where there isn't.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("b6:b14")
Application.EnableEvents = False
If Not Intersect(Target, VRange) Is Nothing Then
If Target.Offset(0, -1) = "" Then 'no employee!
MsgBox "There is no employee for that shift...entry deleted"
Target = ""
Application.EnableEvents = True
End If
End If
End Sub

Any takers on this one?

Thanks, Dave

Dave

It looks like Application.EnableEvents = True is in the wrong location



try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("b6:b14")
Application.EnableEvents = False
If Not Intersect(Target, VRange) Is Nothing Then
If Target.Offset(0, -1) = "" Then 'no employee!
MsgBox "There is no employee for that shift...entry deleted"
Target = ""
End If
End If
Application.EnableEvents = True
End Sub

So basically, I was not allowing the code to "finish" for all tested values, by placing that line within the loop(s)...

Again, thanks for seeing the obvious, and posting so quickly!