Adding a new comment with a function?

[Cheese]

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?

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

[Cheese]

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!

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!