I'm using the below code to run a macro that add comments to cells. Does
anyone know if there is anyway I can edit the code so that when the
comment box is generated it contains text from a specific cell within
the workbook? I would like to do it this way as the changes requently.


ActiveCell.AddComment
ActiveCell.Comment.Text Text:=""
ActiveCell.Comment.Visible = True

With ActiveCell.Comment.Shape.OLEFormat.Object
..Font.Name = "Arial"
..Font.Size = 20
..Font.Bold = True
..Width = 400
..Height = 300

End With


End Sub


C.






------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Coco,

see following 2 variations on the theme..


Sub DoComment1()
With ActiveCell
.ClearComments
With .AddComment("NOTE" & vbLf & Worksheets(1).Range("$a$1").Value)
.Shape.TextFrame.Characters(1, 4).Font.Bold = True
.Shape.TextFrame.Characters(1, 4).Font.Size = 14
.Visible = True
End With
End With
End Sub

Sub DoComment2()
Dim rTgt As Range
Dim rSrc As Range
Dim cNew As Comment

Set rSrc = Application.InputBox( _
Prompt:="Please indicate source", Type:=8)
Set rTgt = ActiveCell
rTgt.ClearComments
Set cNew = rTgt.AddComment("")
With cNew
.Text ("NOTE" & vbLf & rSrc.Value)
.Visible = True
With .Shape.TextFrame.Characters(1, 4)
.Font.Bold = True
.Font.Color = vbRed
.Font.Size = 14
End With
End With
End Sub







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


coco wrote:

I'm using the below code to run a macro that add comments to cells.
Does anyone know if there is anyway I can edit the code so that when
the comment box is generated it contains text from a specific cell
within the workbook? I would like to do it this way as the changes
requently.


ActiveCell.AddComment
ActiveCell.Comment.Text Text:=""
ActiveCell.Comment.Visible = True

With ActiveCell.Comment.Shape.OLEFormat.Object
Font.Name = "Arial"
Font.Size = 20
Font.Bold = True
Width = 400
Height = 300

End With


End Sub


C.






------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/