Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a variable value to a comment?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a variable value to a comment?
Sub PutFormulaInComment()
With ActiveCell .AddComment (.Formula) End With End Sub HTH -- AP "Tom" a écrit dans le message de ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a variable value to a comment?
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 Tom wrote: 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! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a variable value to a comment?
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? -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=520663 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a variable value to a comment?
Yes, use an event macro Private Sub Worksheet_Change(ByVal Target As Range) and add the very same code ... HTH Cheers Carim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a variable value to a comment?
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 bhofsetz wrote: 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? -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=520663 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a picture to a comment box | Excel Discussion (Misc queries) | |||
Adding a comment to a cell in VBA?! | Excel Programming | |||
Adding a new comment with a function? | Excel Discussion (Misc queries) | |||
Adding Comment to cell | Excel Programming | |||
Adding comment programmatically | Excel Programming |