Why Won't This Worksheet_Change Work?!!!

Fuddy_Duddy

New Member
Joined
Apr 24, 2024
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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?
 
Upvote 0
How is Range("D10") being changed? manually, by code or by formula?
 
Upvote 0
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.
 
Upvote 0
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?

VBA 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
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
 
Upvote 0
Please note that I have just amended the instructions
 
Upvote 0
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?
 
Upvote 0
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?

VBA 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
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top