Target.Value issue

[erutherford]

Thought this would be simple, but I guess not. Column B is what is looked at to determine what goes in the target.offset. Or at least it should? What am I missing?

Private Sub Worksheet_Change(ByVal Target As Range) 'Auto entry for OK entries

Application.EnableEvents = False

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Cells.Value = " " Or IsEmpty(Target) Then Exit Sub
    
    If Target.Value = "ok" Then Target.Offset(0, 1).Value = "0" 'Jumps to Col.D - Auto entry
    If Target.Value = "ok" Then Target.Offset(0, 5).Value = "4" 'Jumps to Col H - Auto entry
    If Target.Value = "ok" Then Target.Offset(1, 0).Select 'Jumps to Col H - Auto entry
    If Target.Value <> "ok" Then Target.Offset(0, 1).Select
    
Application.EnableEvents = True
end if
end Sub

 

[JLGWhiz]

Do you have any cells merged with column B or do you have any columns hidden so that column 2 is actually column C?

 

[erutherford]

Just checked, no merged cells or hidden.

 

[erutherford]

I did a very simple test. New workbook and used some very simple code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.Color = vbRed
End Sub

This would worked fine, but once I switched over to another workbook, then switched back it stopped working! I did an update for the program and it works flawless now. I don't like those solutions, but I couldn't see anything wrong with the code. Working now, till the next bug!

 

[JLGWhiz]

Must be grimlins, thanks for the feedback,
regards, JLG

 

[erutherford]

Took my file home and checked it there. Made sure the app was updated (was current). The code worked fine for about 10 entries, then stopped! If I saved, closed and reopened, the code worked fine. Strange to say the least. Problem not solved yet! Back to the simple code workbook to solve this mystery.

 

[dmt32]

Part problem you are likely to have with your code is that your are re-setting EnableEvents ONLY within your If construct. Once envents are disabled, they must be explicitly re-set to True otherwise they will not function.

try moving this line

Application.EnableEvents = True
End If

outside the Endif

End If
Application.EnableEvents = True

You will probably first need to manually re-set with following code

Sub ResetEvents()
Application.EnableEvents = True
End Sub

Dave

 

[erutherford]

That seems to have worked and I understand what you are saying about resetting after the event is finished.
Thanks for the solution!

 

[dmt32]

That seems to have worked and I understand what you are saying about resetting after the event is finished.
Thanks for the solution!

easily overlooked

You will need to correct this line

If Target.Cells.Value = " " Or IsEmpty(Target) Then Exit Sub

otherwise you will continue to have the same problem
If that condition is met then send code to a label rather than exit sub

e.g

Private Sub Worksheet_Change(ByVal Target As Range) 'Auto entry for OK entries

Application.EnableEvents = False

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    If Target.Cells.Value = " " Or IsEmpty(Target) Then GoTo exitsub
   

End If

exitsub:
Application.EnableEvents = True
End Sub

or you could re-write the code so events are only false inside your Target range maybe

Dave

 

[erutherford]

Go it! thanks