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?

 

[Andrew Poulsom]

You can't put a formula in a comment, so you will need to use the Worksheet_Change event procedure to update them. If you need more help give a few examples of which cells you want to link.

 

[LEVICC01]

Brilliant thank you.

Sheet Summary Cells F4/F5 (Merged) - Links to Index D297
Sheet Summary Cells F12/F13 (Merged) - Links to Index D298
Sheet Summary Cells F19/F19 (Merged) - Links to Index D299
and so on.

 

[Andrew Poulsom]

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

 

[LEVICC01]

Thanks, i have copied this into the sheet and it isnt working.

Sorry to be a pain.

 

[Andrew Poulsom]

In what way isn't it working? It worked for me when I changed something in F4, F12 or F19.

 

[LEVICC01]

Ah, ok, i think i know what its going to do.

I cant make a change in the cells, as i have the conditional formattign for the traffic lighting in those specific cells too, does that stop the code working?

 

[Andrew Poulsom]

Sorry I don't understand. How does conditional formatting prevent you from changing the text in a cell? You said you wanted the comment to change when you changed the definition.

 

[LEVICC01]

Sorry, the cell has a value of a 1, 2 or a 3 in it already, which it reads from another cell, and determines whether it shows a red, amber or a green circle.

If i change the value in it, it will throw out the formatting. It's in Excel 2007 so i get a nice traffic light effect for the report.

I've tried pressing F2 on the cell, and then enter (so i dont change the value), but it doesnt seem to do anything.

 

[Andrew Poulsom]

The values 1, 2 and 3 don't look to me like definitions that would appear in a comments box. The Workeheet_Change event won't fire when the result of a formula changes.