Linking Comments Box contents to a cell in another sheet

[LEVICC01]

Hi all,

I have a set of key measures on one sheet, and on another, a definitions sheet that can be printed.

I would like a comments box on each of my measures that reads from the definitions sheet, so that if i change a defintiion, it changes in the comments box automatically.

Please help?

 

[svk01]

Try this in the module for the Summary worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("Index")
        Select Case Target.Address
            Case "$F$4"
                .Range("D297").Comment.Text Text:=Target.Value
            Case "$F$12"
                .Range("D298").Comment.Text Text:=Target.Value
            Case "$F$19"
                .Range("D299").Comment.Text Text:=Target.Value
        End Select
    End With
End Sub

Hi Andrew - thank you for this elegant guidance, it perfectly addresses a problem I am working on. However, when I try to run this code I continuously receive an "argument not optional" error and despite many hours of debugging I cannot figure out how to fix it. For your reference, I have attached the below code to a sheet called "All Responses." This sheet contains data that has been copied into comments placed in a second sheet within the same workbook called "HeatMap - Strengths+Weaknesses." As you can probably tell, the locations of the originating cells in "All Responses" mirrors those in "HeatMap - Strengths+Weaknesses" because the aim here is to use the comment to elaborate on a score given to each response in "All Responses." Do you have any idea why the compiler is not receiving an argument and is thus outputting this error?


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("HeatMap - Strengths+Weaknesses")
Select Case Target.Address
Case "$F$2"
.Range("F2").Comment.Text Text:=Target.Value
Case "$F$3"
.Range("F3").Comment.Text Text:=Target.Value
End Select
End With
End Sub

 

[Fluff]

There is nothing wrong with that code.
As this is a very old thread I suggest you start a new thread of your own & let us now which part of the code is highlighted.
You have probably got a procedure or variable that is also a keyword. Such as Text, Comment etc.

 

[svk01]

Okay, I will do that thank you! By the way, it doesn't highlight any code when the error is outputted so I don't know which area is problematic.