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

 

[VoG]

Try

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

 

[Ken Russell]

Try

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

I made the change :
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

But it didn't work.

Ken

 

[VoG]

It works for me. Where did you put the code? You have to right click the sheet tab, select View Code and paste in the code.

The FillComments sub should go in a regular module.

 

[Ken Russell]

My macro code FillComments is in Module 1 and the code you provided is in Sheet 5.

I now get a Compile Error - "Text" Invalid Outside Procedure.

 

[VoG]

Make sure that Sheet5's code is just

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

without any extraneous statements outside that procedure.

 

[Ken Russell]

Make sure that Sheet5's code is just

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

without any extraneous statements outside that procedure.

Yes it is exactly that.

I've just noticed that the compile error refers to my macro:

Application.Goto Reference:="Text"
Dim Cell As Range
Selection.ClearComments
For Each Cell In Selection
Cell.AddComment Cell.Text
Cell.Comment.Visible = False
Cell.Comment.Shape.TextFrame.AutoSize = True
Next Cell


Dim C As Comment, WS As Worksheet
Const TextToFind As String = ""
For Each WS In Worksheets
For Each C In WS.Comments
If UCase(C.Text) = UCase(TextToFind) Then C.Delete
Next

Next
Application.Goto Reference:="Text"
Selection.Copy
Application.Goto Reference:="Import"
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto Reference:="Text"
Application.CutCopyMode = False
Selection.ClearComments
Range("K28").Select
End Sub

 

[VoG]

You would need to code like this

Application.Goto Reference:=Range("Text")

wherever you use Application.GoTo

 

[Ken Russell]

I made the change to all the GoTo instructions but I still get eraxactly he same error.

I really appreciate your patience.

Cheers,
Ken

 

[VoG]

You must still have "Text" somewhere that is not wrapped in Range()