Adding a new comment with a function?
 

Is there a method to add a comment to a particular cell by using a function
in another open cell?

I might write some comments in an adjacent empty cell (horizontally
speaking) because this is much faster than manually creating individual
comments. I would like to automatically apply those comments to another range
of cells, but as comments to those cells. Any ideas?

Hi Cheese,

The following macro inserts the text of each cell in rng1 into comments in
each cell in rng2. If the comments do not already exist, they are created.

Public Sub Tester03()
Dim rng1 As Range, rng2 As Range, rCell As Range
Dim sStr As String
Dim sh As Worksheet
Dim i As Long

Set sh = ActiveSheet '<<======= CHANGE
Set rng1 = sh.Range("A1:A20") '<<======= CHANGE
Set rng2 = rng1.Offset(0, 1) '<<======= CHANGE

For i = 1 To rng2.Cells.Count
sStr = rng1(i).Text
With rng2(i)
If .Comment Is Nothing Then .AddComment
.Comment.Text Text:=sStr
End With
Next

End Sub


---
Regards,
Norman



"Cheese" wrote in message
...
Is there a method to add a comment to a particular cell by using a
function
in another open cell?

I might write some comments in an adjacent empty cell (horizontally
speaking) because this is much faster than manually creating individual
comments. I would like to automatically apply those comments to another
range
of cells, but as comments to those cells. Any ideas?

Dear Norman,

Thanks for the help. Your macro worked great. Is there a way to resize the
comment if the text doesn't fill the box, so the comment doesn't appear any
larger than necessary? Sometimes it covers up other cells when it doesn't
need to.

Thanks!

Hi Cheese,

Is there a way to resize the comment if the text doesn't fill the box,
so the comment doesn't appear any larger than necessary?

Try this version which addresses the size issue. Note that the sizing code
is borrowed from an original Dave Peterson procedure.

'========================================
Public Sub Tester03A()
Dim rng1 As Range, rng2 As Range, rCell As Range
Dim sStr As String
Dim sh As Worksheet
Dim i As Long
Dim lArea As Long

Set sh = ActiveSheet '<<======= CHANGE
Set rng1 = sh.Range("A1:A20") '<<======= CHANGE
Set rng2 = rng1.Offset(0, 1) '<<======= CHANGE

For i = 1 To rng2.Cells.Count
sStr = rng1(i).Text
With rng2(i)
If .Comment Is Nothing Then .AddComment
.Comment.Text Text:=sStr
With .Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.2
End If
End With
End With
Next

End Sub

'<<========================================

---
Regards,
Norman



"Cheese" wrote in message
...
Dear Norman,

Thanks for the help. Your macro worked great. Is there a way to resize the
comment if the text doesn't fill the box, so the comment doesn't appear
any
larger than necessary? Sometimes it covers up other cells when it doesn't
need to.

Thanks!

Hi Cheese,

And to avoid creating empty comments if no text is entered in the
corresponding comment text cell, try:

Public Sub Tester03B()
Dim rng1 As Range, rng2 As Range, rCell As Range
Dim sStr As String
Dim sh As Worksheet
Dim i As Long
Dim lArea As Long

Set sh = ActiveSheet '<<======= CHANGE
Set rng1 = sh.Range("A1:A20") '<<======= CHANGE
Set rng2 = rng1.Offset(0, 1) '<<======= CHANGE

For i = 1 To rng2.Cells.Count
sStr = rng1(i).Text
With rng2(i)
If Not IsEmpty(rng1(i)) Then
If .Comment Is Nothing Then .AddComment
.Comment.Text Text:=sStr
With .Comment
.Shape.TextFrame.AutoSize = True
If .Shape.Width 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.2
End If
End With
End If
End With
Next

End Sub

---
Regards,
Norman



"Cheese" wrote in message
...
Dear Norman,

Thanks for the help. Your macro worked great. Is there a way to resize the
comment if the text doesn't fill the box, so the comment doesn't appear
any
larger than necessary? Sometimes it covers up other cells when it doesn't
need to.

Thanks!