How do I display the formula *inside* of an Excel "comment"
automatically?  And how do I get the formula to automatically update
when new rows/columns are added/deleted?

---

Request for Question Clarification by cynthia-ga on 07 Feb 2006 10:20 PST

Is this on the right track?

Excel: formula in comments? 
http://actuary.ca/actuarial_discussion_forum/showthread.php?p=1021584

I found several ideas, but more info is needed to get the right fix
for your specific problem...


Can you explain *exactly* what you want to do?

---

Clarification of Question by foutaine-ga on 10 Feb 2006 03:00 PST

Perfecto!  I was able to modify it enough for my purposes.  Much appreciated!

Hi foutaine,

Thanks for accepting the resource I found as your Answer, I'm glad it
worked for you!

Here it is again, in the Answer Box:

Excel: formula in comments? 
http://actuary.ca/actuarial_discussion_forum/showthread.php?p=1021584
..."Is it possible to put a cell reference in a comment? I want to
have what is in a comment dependent on a cell values in another
page..."

Post 06-03-2005, 04:05 PM by GatesIsAntichrist: 

..."Enclosed is code to store a cell's formula into its comment. It's
nifty in that [if you ask it to] it will copy the formula from the
comment back into the cell. Perhaps you can adapt it for what you
asked for.

I keep this in PERSONAL.XLS. It is "safe" to play with as is (as far
as overwriting goes); it has plenty of msgbox prompts..."

Code:

*******************************************************************

Sub CommentFormula()
  Dim bEqual    As Boolean
  Dim iEqual    As Integer, iLen As Integer, iLoc As Integer
  Dim sAuthor   As String, sComment As String, sCR As String, sCR2 As String
  Dim sDate     As String, sFormula As String, sResponse As String, sTime As String
    bEqual = False
    sAuthor = Application.UserName
    sCR = Chr(10): sCR2 = sCR & sCR
    sDate = Format(Date, "yyyy.mm.dd"): sTime = Format(Time, "hh:mm")
    sFormula = ActiveCell.Formula
    iLen = Len(sFormula)
    For iEqual = 1 To iLen
        If Mid(sFormula, iEqual, 1) = "=" Then bEqual = True: Exit For
    Next
    If bEqual = False Then sFormula = ActiveCell.Value
    With ActiveCell
        On Error GoTo ErrorHandler:
        .AddComment
        .Comment.Text Text:=sAuthor & sCR & sDate & "  " & sTime & sCR2 & sFormula
        If Application.DisplayCommentIndicator <>
xlCommentAndIndicator Then .Comment.Visible = False
    End With
Exit Sub
ErrorHandler: 'This should only hit when a comment preexists, which is
not necessarily "bad"
    On Error GoTo 0
    ActiveCell.Comment.Visible = True
    sResponse = MsgBox(prompt:="Copy Formula from Comment to Cell?", _
                        buttons:=vbYesNo, Title:="Comment Formula")
    If sResponse = vbYes Then
        sComment = ActiveCell.Comment.Text
        iLen = Len(sComment)
        For iEqual = 1 To iLen
            If Mid(sComment, iEqual, 1) = "=" Then bEqual = True: Exit For
        Next
        If bEqual = False Then
            iLoc = Application.WorksheetFunction.Find(sCR2, sComment, 1)
            sFormula = Right(sComment, iLen - iLoc - 1)
            ActiveCell.Value = sFormula
        Else
            iLoc = Application.WorksheetFunction.Find("=", sComment, 1)
            sFormula = Right(sComment, iLen - iLoc + 1)
            ActiveCell.Formula = sFormula
        End If
        Resume Next
    End If
    'sResponse is No,to "using" comment
    sResponse = MsgBox(prompt:="OVERWRITE Comment with Cell Formula?", _
                        buttons:=vbYesNo, Title:="Comment Formula")
    If sResponse = vbYes Then
        If bEqual = False Then sFormula = ActiveCell.Value Else
sFormula = ActiveCell.Formula
        ActiveCell.Comment.Delete
        ActiveCell.AddComment 'now,
    Else 'Overwrite sResponse is No
        sResponse = MsgBox(prompt:=" ** Erase ** Comment ??",
buttons:=vbYesNo, Title:="Comment Formula")
        If sResponse = vbYes Then ActiveCell.Comment.Delete
        Exit Sub
    End If
    Resume Next
End Sub

*******************************************************************

Also, much thanks to GatesIsAntichrist for the code!

If I can be of further assistance, please don't hesitate to ask, and
thanks for using the Google Answers service!


~~Cynthia


Search terms used at Google:
excel "formula in comments"

Accurate the first time and timely.