I have tested the macro with 3000 chars in the source cells using Excel 2003

Excel cuts off the cell Comment text at 1024 chars, which is the viewable
number of chars in a cell, even though 3000 are visible in the formula bar.

This viewable limit is a built-in limit of Excel 2003 and older. See
"specifications" in help.

So..........referring to source cells on other sheet will not get you any
more chars.

I believe Excel 2007 will show about 8000 chars but I can't find a number in
2007 help.

Perhaps someone more conversant with 2007 can confirm that number?

In 2007 I placed 4000 chars in a cell..............the macro entered all
4000 into the Comment.


Gord


On Thu, 26 Feb 2009 07:05:03 -0800, andy62
wrote:

Thanks Gord, it works great. But I have done something that is causing
really long text to get cut off. Can you see the issue?

The macro copies in content from cells 50 rows down (your Offset function).
Those cells actually house formulas like this that dynamically grab text from
another worksheet:

=INDIRECT("RowToCopy!BT"&B3)

The text in the source cell (RowToCopy!BT12, for instance) might have 3000
characters. The formula above seems to display only a portion of those
characters, but I was hoping the whole text was available even though not
displayed. But then running the macro copies even less of the text into the
comment box. Am I hitting up against some laws of physics here, working with
too many characters in a cell? Since I am Looking at only 4 cells, should I
skip the middle step and, inside the macro, somehow refer directly to those
source cells on the other worksheet?

TIA

"Gord Dibben" wrote:

Sub UpdateComments()
'
' UpdateComments Macro
' Macro recorded 02/25/2009
'
Dim cmt As Comment
Dim r As Range
For Each r In Range("D4:F4,D13")
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)