Sub UpdateComments()
'
' UpdateComments Macro
' Macro recorded 02/25/2009
'
Dim cmt As Comment
Dim r As Range
For Each r In Range("D4:F4")
r.ClearComments
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(50, 0).Text
With r.Comment
.Shape.TextFrame.AutoSize = True
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200 'adjust to suit
.Shape.Height = 200 'adjust to suit
End With
End If
Next r
End Sub

Adjust Width and Height to numbers you like.

Note: this can be run from a Worksheet_Calculate event if that's what you
want.

Right-click on the sheet tab and "View Code". Copy/paste this into that
sheet module.

Private Sub Worksheet_Calculate()
Call UpdateComments
End Sub


Gord

On Wed, 25 Feb 2009 13:20:05 -0800, andy62
wrote:

I think a macro published last year by Gord is going to meet my needs, but
with a minor adjustment. Show bellow is my current version of Gord's macro
to load the text from a cell into the comment box of another cell. It works
the first time, but I need to be able to run the macro numerous times to
reload different content into the comment box. The destination cells stay
the same - they are actually formulas that pull in data based on an indirect
reference to another sheet, based on a row number entered by the user. Also,
I need to make the comment boxes huge; if I delete them each time they get
reset to the default (tiny).

TIA

My verson of Gord Dibben's macro:

Sub UpdateComments()
'
' UpdateComments Macro
' Macro recorded 02/25/2009
'
Dim cmt As Comment
Dim r As Range
For Each r In Range("D4:F4")
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(50, 0).Text
End If
Next r
End Sub

(with thanks to Gord Dibben MS Excel MVP)