Worksheet_SelectionChange Not Firing

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

Code works sometimes, but no firing at times.
Any clues?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("j1:j65000")) Is Nothing
Then
If Selection.Value <> "DONE" Then
Selection.Value = "DONE"
Range("A" & Selection.Row & ":j" &
Selection.Row).Interior.ColorIndex = 15
Else
Selection.Value = ""
Range("A" & Selection.Row & ":j" &
Selection.Row).Interior.ColorIndex = xlNone
End If


End If
Application.EnableEvents = True
End Sub
 
JW:

Are you clicking outside of a J cell before clicking back into it? The
section change event doesn't fire if you click a cell a second time once
you've already clicked on it; so if you're in H, then click on a J cell, it
fires. But then if you click the same cell, it won't; you would need to
click OUTSIDE that J cell and BACK in it (thus CHANGING the selection).

HTH
 
i did the other day and it didn't work. Now, it works and the other
code has stopped.
Go figure... Thanx!
 
This is a guess.

If you're testing your code, you may have stopped the execution in the middle of
the code -- when the enableevents setting was turned off.

That means that you'll have to turn it back on for excel to start monitoring for
those events.
 
Besides what has been mentioned by others, I would add a test to see
if more than one cell has been selected, as this will cause an error,
in which case the event handler is disabled. Also I prefer to use
'Target' as range reference as opposed to Selection. If you are using
a Change event, Target will be different from selection!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("J")) Is Nothing Then
Debug.Print Target.Address, " & ", Selection.Address
If Target.Value <> "DONE" Then
Target.Value = "DONE"
Range("A" & Target.Row).Resize(1, 10).Interior.ColorIndex = 15
Else
Target.Value = ""
Range("A" & Target.Row & ":j" & Target.Row).Interior.ColorIndex =
xlNone
End If
End If
Application.EnableEvents = True
End Sub

Regards,
Per
 
Back
Top