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]

I'll change the range name to something else and give ita try tomorrow.

Goodnight from Australia.

 

[Ken Russell]

I have changed the ranges to different names but it still reports a compile error in the first line. I then replaced the range name with cell references - same error.

Very confusing......

Ken

 

[VoG]

Please can you post all of the code that you have in Module 1 - please use code tags

[code]
code goes here
[/code]

 

[Ken Russell]

Please can you post all of the code that you have in Module 1 - please use code tags

[code]
Application.Goto Reference:=Range("Dave1")


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:=Range("Dave1")


Selection.Copy
Application.Goto Reference:=Range("Dave2")
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto Reference:=Range("Dave1")


Application.CutCopyMode = False
Selection.ClearComments
Range("K28").Select
End Sub
[/code]

This code is actually in Module 4

Ken

 

[VoG]

You are missing the Sub declaration

Sub FillComments()
Application.Goto Reference:=Range("Dave1")


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:=Range("Dave1")


Selection.Copy
Application.Goto Reference:=Range("Dave2")
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto Reference:=Range("Dave1")


Application.CutCopyMode = False
Selection.ClearComments
Range("K28").Select
End Sub

 

[Ken Russell]

A new compile error: Constant Expression Required
If UCase(C.Text) = UCase(TextToFind) Then C.Delete

 

[VoG]

Try without UCase

If C.Text = TextToFind Then C.Delete

 

[Ken Russell]

Same error - it highlights TextToFind.

Ken

 

[VoG]

Tested and working with no error here.

 

[Ken Russell]

I give up!

Thanks for trying.

Ken