Why Won't This Worksheet_Change Work?!!!

F

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
Sort by date Sort by votes
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
MARK858 said:
How is Range("D10") being changed? manually, by code or by formula?
Click to expand...
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
MARK858 said:
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
Click to expand...
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
kevin9999 said:
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?
Click to expand...
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
You must log in or register to reply here.

Similar threads

L
  • Question
form excel file open another excel file and .unprotect that file copy one row and .protect that file and close works only once
Replies
2
Views
327
lodewijk
L
F
  • Solved
Error '-2147319767 (80028029)'
Replies
3
Views
332
RoryA
RoryA
Y
  • Solved
Highlighting selected words in a column.
Replies
6
Views
287
Akuini
Akuini
G
  • Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
90
GK039
G
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
346
offthelip
O

Forum statistics

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

Share this page

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