Add Comments with Text From Cells

[coco]

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.

 

[keepitcool]

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 >