Function in Comment - Possible ?

[kenstr]

Is it possible to have a function in a cell comment ?

Say something like " =$c$4 "

Thanks,

Ken

 

[Juan Pablo González]

I have never downloaded it but in here:

http://www.bmsltd.co.uk/MVP/MVPPage.asp#DavidHager

you'll probably find your answer...

 

[Damon Ostrander]

Hi Ken,

No, a comment can't evaluate a formula. However a simple VBA event macro can accomplish the same thing. For example, the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$4" Then
Target.Comment.Shape.TextFrame.Characters.Text = [c4].Text
End If
End Sub

will update the comment in cell B4 to contain whatever is displayed in cell C4 whenever cell B4 is selected (it must be selected--not just a mouseover). In addition, the cell B4 must already contain a comment since this code just modifies the existing comment.

This code must be inserted in the worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and insert the code in the code pane.

Damon

 

[Juan Pablo González]

Damon, it can actually, by using a bit of code, I just downloaded that workbook I linked above, and it works pretty cool...

 

[Damon Ostrander]

Thanks Juan Pablo,

Yes, Dave Hager's solution is a very interesting one. I'm not sure I would officially say that the comment is calculating the formula since it requires VBA to evaluate it and return the value, but it appears to work as if it were, except that it apparently doesn't recognize dependencies.

I keep forgetting the power of the Evaluate function!

Damon