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
 
Sort by date Sort by votes

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Fuddy_Duddy said:
I placed a message box before and after the Open Filename and got the message each time, yet the code did not execute
Click to expand...
If you get the message boxes then the code is executing, try putting the message box only where I placed it as I am trying to test if it is getting into the If statement
 
Upvote 0
Found an easy solution...just used a call of the macro that worked...and it worked. Used a few iferrors to correct the error values, and success! Thanks to those that responded.
Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)

'CALLS The Macro

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

Call Extract_Number_of_Lines_from_Scene

End If

End Sub
 
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