SelectionChange stopped working

[Dav1d]

The code below created a new line below when a cell in column F was changed (a dropdown menu). I'm certain it was working perfectly but now it isn't.

Can someone anyone see a problem with the code, please?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F:F")) Is Nothing Then
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
ActiveSheet.Protect AllowDeletingRows:=True
On Error GoTo 0
End If
Application.EnableEvents = True
End Sub

 

[prajul89]

See the Macro Security Setting.

 

[RoryA]

Your code runs when you select a different cell, not when you change a cell.

 

[Dav1d]

Hi Rory

Thanks for the rapid [immediate!] reply, I really appreciate your time.

The macro doesn't fire even when I move out of the F column. Nothing happens.

(The security level is generally set medium but at low it doesn't either).

 

[RoryA]

The code only fires if you select a cell in column F. If it's still not firing, have you had any error messages?

 

[Hermanito]

You probably interrupted your code one time, and it didn't get to the line
Application.EnableEvents = True

You need to put that in the immediate window and press enter, so that it is again enabled.

 

[Dav1d]

The code only fires if you select a cell in column F. If it's still not firing, have you had any error messages?

No error message at all. Nothing at all happens

 

[Hermanito]

Have you tried my suggestion??

 

[Dav1d]

Sorry, Hermanito, I'd typed the reply but didn't hit send. One of those mornings.

Yes, I tried that. I even retyped the whole macro as a start again approach but no difference.

 

[Hermanito]

try setting a breakpoint on the first line of code inside the eventhandler.
Then go in your worksheet and perform the action that should trigger the event... if you don't get thrown into the code in debugmode, it means your events are still disabled somehow...
Otherwise you can step through the rest of the code and check why it doesn't work as expected...

Have you tried closing and reopening Excel?