For Gord or other: dynamic comment

[andy62]

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)

 

[Gord Dibben]

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

 

[andy62]

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]

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

 

[andy62]

Thanks for investigating, Gord. I wonder if assigning the text in a
different way would help? When I go to the source cell, copy the text itself
(not just copy the cell but go into edit more and copy the contents) and then
paste the contents into the same comments box, I get all 3000 characters.
Does the macro copy the text in this way, and if not, could it?

Andy

 

[Gord Dibben]

This revision will put all 3000 chars in a Comment in XL2003

It basically copies the text from the formula bar.

Sub UpdateComments22()
Dim cmt As Comment
Dim rtext As String
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
rtext = r.Offset(50, 0).Value
cmt.Text Text:=rtext
With r.Comment
.Shape.TextFrame.AutoSize = True
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 600 'adjust to suit
.Shape.Height = 600 'adjust to suit
End With
End If
Next r
End Sub


Gord

 

[andy62]

Thanks, Gord for your diligence. It works great! Since the cells with the
content were actually formulas, I added some simple code that copies all of
them down one row, as Values, to produce the actual content in the formula
bar each time.

 

[Gord Dibben]

With the revised code I have no problem getting all text into the Comments
even when the source cells are formulas.

On Sheet2 I have a formula in A1 =REPT("qwerty ",500) which gives me 3500
characters.

On Sheet1 I have a formula =Sheet2!A1

I use this as source cell for the Comment

I don't see a need for the copy as values step.

But good to hear you're sorted out.


Gord