Run macro if any cell in a named range changes

[Ken Russell]

I'm tearing my hair out trying to get this to work.

I have a range named Text in a sheet in a workbook. I would like the macro FillComments to run when any cell in that range is changed.

I can't see why the following doesn't work

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target.Addresss = Range("Text")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call FillComments
Application.EnableEvents = True
End Sub

Any help would be appreciated by this 81 year old

Cheers,
Ken

 

[Ken Russell]

One last try.

The macro worked perfectly as a stand alone invoked by Ctrl + Shift + c, it also worked using Workbook events Open and also Close. It only fails with the Change event.

Does that give any clues/

Cheers,
Ken

 

[VoG]

You could try commenting out the line as shown but I can't see why that line would cause your other code to fail

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Text")) Is Nothing Then Exit Sub
'Application.EnableEvents = False
Call FillComments
Application.EnableEvents = True
End Sub

 

[Ken Russell]

You could try commenting out the line as shown but I can't see why that line would cause your other code to fail

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Text")) Is Nothing Then Exit Sub
'Application.EnableEvents = False
Call FillComments
Application.EnableEvents = True
End Sub

I now reeive this error:

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed.

 

[VoG]

On which line of code (click Debug)

 

[Ken Russell]

If Intersect(Target, Range("Text")) Is Nothing Then

Ken

 

[VoG]

Is your named range still called Text?

 

[Ken Russell]

No, they are now called Dave1 and Dave2.

 

[VoG]

In that case

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Dave1")) Is Nothing Then Exit Sub ' or is it Dave2?
'Application.EnableEvents = False
Call fillcomments
Application.EnableEvents = True
End Sub

 

[Ken Russell]

I got confused. The Range Name Dave2 doesn't apply to this Workbook Event even though it exists in the macro. That probably changes your resposne. Sorry about that.

Ken

 

[Ken Russell]

SOLVED!!!!

The following Workbook Event works perfectly:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Dave1")) Is Nothing Then
FillComments
End If
End Sub