Why Won't This Worksheet_Change Work?!!!

[Fuddy_Duddy]

This Worksheet_Change refuses to work. When I execute it from a macro, it works fantastically. But not as a Worksheet_Change. And it's the only change for the sheet.

Please help.

Thanks in advance.

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("D10").Address Then

' Gets the Number of Lines-to-Date from the Individual "COPY_for_Recipe_" File

Dim openFilename As String
Dim LineAnswer As String

openFilename = Range("K9")

If Range("K9") <> "ENTER FILE NUMBER" And Range("D11") <> "NO SELECTION" Then

Workbooks.Open Filename:= _

"D:\DIGITAL RESOUIRCE, LLC\COOKING_CHANNEL\COPY_for_EPISODES_AQR\COPY_for_" & openFilename & ".xlsm"

Windows("COPY_for_" & openFilename & ".xlsm").Activate

Sheets("Script").Select

Sheets("Script").Unprotect

LineAnswer = Range("AZ2")

Sheets("Script").Protect

Windows("Word_Count_Utility.xlsm").Activate

Sheets("WORD_COUNT").Select

Sheets("WORD_COUNT").Unprotect

Range("O12") = LineAnswer

Sheets("WORD_COUNT").Protect

End If

End If

 

[JoeMo]

Possibly, you have inadvertently disabled events.
While viewing your code in the VBE, press ctrl+g to open the Immediate Window.
In the Immediate window type (w/o the quote marks): "Application.EnableEvents = True" and press enter.
Now click on D10 in your worksheet and make a change to it. Does the code run?

 

[Fuddy_Duddy]

Thank you for the response, but unfortunately, it wasn't the solution.

 

[MARK858]

How is Range("D10") being changed? manually, by code or by formula?

 

[Fuddy_Duddy]

How is Range("D10") being changed? manually, by code or by formula?

Manually...inserting a number and hitting the Enter. The strange thing is that when the code was assigned to a macro it worked flawlessly.

 

[MARK858]

If you replace the code with the code below and type xxxx in D10 (with D11 and K9 being blank) do you see the message box?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("D10").Address Then

        ' Gets the Number of Lines-to-Date from the Individual "COPY_for_Recipe_" File

        Dim openFilename As String
        Dim LineAnswer As String

        openFilename = Range("K9")

        If Range("K9") <> "ENTER FILE NUMBER" And Range("D11") <> "NO SELECTION" Then
MsgBox "xxxxxx"
'            Workbooks.Open Filename:= _
'            "D:\DIGITAL RESOUIRCE, LLC\COOKING_CHANNEL\COPY_for_EPISODES_AQR\COPY_for_" & openFilename & ".xlsm"
'
'            Windows("COPY_for_" & openFilename & ".xlsm").Activate
'
'            Sheets("Script").Select
'
'            Sheets("Script").Unprotect
'
'            LineAnswer = Range("AZ2")
'
'            Sheets("Script").Protect
'
'            Windows("Word_Count_Utility.xlsm").Activate
'
'            Sheets("WORD_COUNT").Select
'
'            Sheets("WORD_COUNT").Unprotect
'
'            Range("O12") = LineAnswer
'
'            Sheets("WORD_COUNT").Protect

        End If

    End If

End Sub

 

[MARK858]

Please note that I have just amended the instructions

 

[kevin9999]

Is the module definitely attached to the worksheet and not in a separate module? I.E. in the VB editor, do you see the code when you double click on the sheet in the project explorer?

 

[Fuddy_Duddy]

If you replace the code with the code below and type xxxx in D10 (with D11 and K9 being blank) do you see the message box?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("D10").Address Then

        ' Gets the Number of Lines-to-Date from the Individual "COPY_for_Recipe_" File

        Dim openFilename As String
        Dim LineAnswer As String

        openFilename = Range("K9")

        If Range("K9") <> "ENTER FILE NUMBER" And Range("D11") <> "NO SELECTION" Then
MsgBox "xxxxxx"
'            Workbooks.Open Filename:= _
'            "D:\DIGITAL RESOUIRCE, LLC\COOKING_CHANNEL\COPY_for_EPISODES_AQR\COPY_for_" & openFilename & ".xlsm"
'
'            Windows("COPY_for_" & openFilename & ".xlsm").Activate
'
'            Sheets("Script").Select
'
'            Sheets("Script").Unprotect
'
'            LineAnswer = Range("AZ2")
'
'            Sheets("Script").Protect
'
'            Windows("Word_Count_Utility.xlsm").Activate
'
'            Sheets("WORD_COUNT").Select
'
'            Sheets("WORD_COUNT").Unprotect
'
'            Range("O12") = LineAnswer
'
'            Sheets("WORD_COUNT").Protect

        End If

    End If

End Sub

I placed a message box before and after the Open Filename and got the message each time, yet the code did not execute. I've spent nearly two days trying to see what is wrong. The most frustrating thing I've every encountered. Thanks for trying to help.

 

[Fuddy_Duddy]

Is the module definitely attached to the worksheet and not in a separate module? I.E. in the VB editor, do you see the code when you double click on the sheet in the project explorer?

Yes, it's in a worksheet that has all of its modules working correctly. I've never seen code work out when assigned as a macro, but not fire when a change.