Solved: Is there a way to put a formula in a comment box????

[marreco]

Talk guys, I have a doubt, have looked in several places not found the solution!
Is there any way to put a formula in a comment in excel?
For example, cell A1 of the commentary has to show the value of A2 + A3.:dunno

Thank's

Cross-Post
http://www.excelforum.com/excel-programming/817860-is-there-a-way-to-put-a-formula-in-a-comment-box.html

[mdmackillop]

Something like this?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x
x = Application.Sum(Range("A2:A3"))
With Cells(1,1)
.Comment.Text Text:=CStr(x)
End With

End Sub

[marreco]

Thank you for responding.

But an error occurred.

Run-time Error '91 ':
The object variable or block ariável 'With' is not defined

[omp001]

maybe:
With Cells(1)
.ClearComments
.AddComment.Text Text:=CStr(x)
End With

[marreco]

Nothing happens!

What should I do to insert a comment box, or the code will do it with the result of the formula?

[mdmackillop]

Should be
With Cells(1,1)

[mikerickson]

You could use this UDF. With a formula like
=ValueAndComment(someFormula, A2+A3)

Function ValueAndComment(ValueInCell As Variant, ValueOfComment As String) As Variant
ValueAndComment = ValueInCell

If TypeName(Application.Caller) = "Range" Then
With Application.Caller.Cells(1, 1)
On Error Resume Next
.Comment.Delete
.AddComment Text:=ValueOfComment
On Error GoTo 0
End With
End If
End Function

[marreco]

Hello everybody.

mdmackillop worked with your suggestion. Thank you :rotlaugh:

Because there is shown a different value than it is in the cell?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x
x = Application.Sum(Range("A2:A3"))
With Cells(1, 1)
.ClearComments
.AddComment.Text Text:=CStr(x)
End With

End Sub
|-------------------#-----------|
hello mike erickson, I can not use your UDF.

In a cell ValueAndComment digit = (A1: A2), but as tenoh resulttado # VALUE.

How to appear in the box comentrio?:mkay

[mdmackillop]

Can you post a sample workbook showing exactly what you are after?

[marreco]

I am a fool.:banghead:
His response was exactly my problem and works perfectly!:thumb

I was putting the result in cell A3, but I was wrong is cell A4.

forgive my ignorance.

[Paul_Hossler]

Mike- -- that's kind of cool.

I didn't think a UDF could do anything to a worksheet (change font, interior, etc.) so I was surprised that you could add a comment like that

I experimented a little since you've shown me the way :clap: and you can get worksheet data and some other stuff into the comment.

I'm going to use your technique to add a little polish to some templates we use


Option Explicit
Function ValueAndComment(ValueInCell As Variant, ValueOfComment As String) As Variant

ValueAndComment = ValueInCell

If TypeOf Application.Caller Is Range Then
With Application.Caller.Cells(1, 1)
On Error Resume Next
.Comment.Delete
' .AddComment Text:=ValueOfComment & vbCrLf & vbCrLf & ValueInCell
' .AddComment Text:=ValueOfComment & vbCrLf & vbCrLf & TestTranslate(ValueInCell)
.AddComment Text:=ValueOfComment & vbCrLf & vbCrLf & Application.Caller.Cells(1, 1).EntireColumn.Cells(1, 1).Value
On Error GoTo 0
End With
End If
End Function

Function TestTranslate(ValueInCell As Variant) As Variant

TestTranslate = ValueInCell
If Not IsNumeric(ValueInCell) Then Exit Function

Select Case ValueInCell

Case Is < 0
TestTranslate = "Negative"
Case Is = 0
TestTranslate = "Just Zero"
Case Is > 0
TestTranslate = "Positive"
End Select
End Function


Paul

[mikerickson]

Paul,
UDF's can also change the InputMessage and Title of a cell's Validation.

BTW, TestTranslate can be done with either formatting or..
TestTranslate = Format(ValueInCell, """Positive"";""Negative"";""Zero""")

[Paul_Hossler]

BTW, TestTranslate can be done with either formatting or..


That was just a simple test to see / confirm that your Comment-changing UDF would be able to handle some processing

I could see using a VLookup for example to come up with some really useful comments

The other thing I wanted to confirm was that I could 'navigate' the sheet so I wanted to see that I could read the column header of the Caller cell




UDF's can also change the InputMessage and Title of a cell's Validation.


I'll add that to the list of the many things I learned today

Also a useful thing to add in to the templates at work to improve the usability

Paul

[marreco]

Thank you to everyone who posted to help me, thank you thank you!

I really like your help

Now I'm in (thread tools) to try to put the topic as solved, but nothing nãoconsigo.

Simply go to the bottom of the forum but I see no option (Solved).

[mdmackillop]

If you use Chrome, you won't see it.

[marreco]

I believe that I no longer use, what should I do to get my post as solved?

thank you

[macropod]

The following macro adds the formulae to the comments for each selected cell, or even the whole worksheet, and displays the comments in an appropriately-sized box.
Sub FormulaeToComments()
Application.ScreenUpdating = False
Dim RngCmnt As Range, RngCell As Range
'skip over errors caused by trying to delete comments in cells with no comments
On Error Resume Next
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
Set RngCmnt = Range(ActiveSheet.UsedRange.Address)
Else
Set RngCmnt = Range(Selection.Address)
End If
'If the cell contains a formula, turn it into a comment.
For Each RngCell In RngCmnt
With RngCell
'check whether the cell has a formula
If Left(.Formula, 1) = "=" Then
'delete any existing comment
.Comment.Delete
'add a new comment
.AddComment
'copy the formula into the comment box
.Comment.Text Text:=.Formula
'display the comment
.Comment.Visible = True
End If
End With
Next
Application.ScreenUpdating = True
End Sub
The following macro evaluates the comments and stores the result back into the target cell.
Sub CalculateCommentFormula()
On Error Resume Next
For Each TargetCell In CommentRange
If Left(TargetCell.Comment.Text, 1) = "=" Then
With TargetCell
.Value = Evaluate(.Comment.Text)
End With
End If
Next
End Sub
Note that you would need to be careful with this if an update is re-evaluated and dependencies are re-calculated in the wrong order.

[marreco]

Good morning!

I am very glad to judar, much thanks!