Adding a variable value to a comment?

[Tom]

How can I add a variable value to a comment?
I want to create a comment that is based on a simple math function.

The comment tag would display the sum of the current cell plus that of one
other.

Here is what I have so far:
-----------------------
dim holdval, commentval
holdval = ActiveCell.Value
ActiveCell.ForumlaR1C1 = "=RC[0]+RC[-3]"
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
commentval = ActiveCell.Value
ActiveCell.Comment.Text = commentval
ActiveCell.Value = holdval
ActiveCell.Select
---------------------------

Line 7 is where I'm getting all the problems. The error I get is "Assignment
to constant is not permitted". I've also tried this line:

ActiveCell.Comment.Text Text:= commentval

in which case I get an application error.

Am I just trying to do something that is impossible or is there another way
to get a variable value into a comment?

Thanks!

 

[Ardus Petus]

Sub PutFormulaInComment()
With ActiveCell
.AddComment (.Formula)
End With
End Sub

HTH

 

[Dave Peterson]

How about just plopping that value directly into the comment:

Option Explicit
Sub testme01()
With ActiveCell
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
.AddComment Text:=CStr(.Value + .Offset(0, -3).Value)
.Comment.Visible = True
End With
End Sub

How can I add a variable value to a comment?
I want to create a comment that is based on a simple math function.

The comment tag would display the sum of the current cell plus that of one
other.

Here is what I have so far:
-----------------------
dim holdval, commentval
holdval = ActiveCell.Value
ActiveCell.ForumlaR1C1 = "=RC[0]+RC[-3]"
ActiveCell.AddComment
ActiveCell.Comment.Visible = True
commentval = ActiveCell.Value
ActiveCell.Comment.Text = commentval
ActiveCell.Value = holdval
ActiveCell.Select
---------------------------

Line 7 is where I'm getting all the problems. The error I get is "Assignment
to constant is not permitted". I've also tried this line:

ActiveCell.Comment.Text Text:= commentval

in which case I get an application error.

Am I just trying to do something that is impossible or is there another way
to get a variable value into a comment?

Thanks!

 

[bhofsetz]

Is there a way to have the comment automatically update if the cells
referenced change?

i.e. If I change the value of the offset cell can I get it to update
the calculated value in the comment box without running the macro
again?

 

[Carim]

Yes, use an event macro
Private Sub Worksheet_Change(ByVal Target As Range)
and add the very same code ...

HTH
Cheers
Carim

 

[Dave Peterson]

I'm not sure what cells are changing and what comments need to be updated, but
the code is very similar (not quite the same).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'one cell at a time only
If Target.Cells.Count > 1 Then Exit Sub

'only look in column E
If Intersect(Target, Me.Range("E:E")) Is Nothing Then Exit Sub

With Target
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
If IsNumeric(.Value) _
And IsNumeric(.Offset(0, -3).Value) Then
.AddComment Text:=CStr(.Value + .Offset(0, -3).Value)
.Comment.Visible = True
Else
.AddComment Text:="Not Numeric!"
End If
End With
End Sub

Change the column that should be used and adjust the .offset() (both spots) to
point at the ranges that should be used.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste that code into the newly opened code window.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm