I have a worksheet that has a number of comments scattered all over.

I'm using the default 'Edit Comment' Excel function to build them all.

I set the size manually cropping all the extraneous space so the area of the
comment is only aruond the text I want.

Thinking that I'm done, I continue doing development, modifying macros for
other sections. However, when I come back to some of the cells that I've
inserted comments for, I notice that the comment field grows, esp if after
i've 'corrected' the size of the comment field, I write a value to the cell.


Is there any way I can fix the size of the comment field so that it will
keep from doing this ? Or do I have to write a custom macro / form to handle
this ?

Thanks,
Chad

Several of my users have complained about similar problems. Comments are
formatted and then some "evil force" changes them. The following is a
technique to un-do the actions of the "evil force":

1. after formatting the comments, create and run a macro to read the
dimensions of each comment and store them in a public array.

2. create another macro to read the public array and apply the dimensions to
each comment

If the "evil force" attacks, you just run the second macro to re-establish
the dimensions of each comment. The following is an example of how to read
(or write) comment dimensions:


Sub readum()
Dim c As Comment
For Each c In ActiveSheet.Comments
x = c.Shape.Width
y = c.Shape.Height
MsgBox (x & " by " & y)
Next
End Sub

--
Gary's Student


"ChadF" wrote:

I have a worksheet that has a number of comments scattered all over.

I'm using the default 'Edit Comment' Excel function to build them all.

I set the size manually cropping all the extraneous space so the area of the
comment is only aruond the text I want.

Thinking that I'm done, I continue doing development, modifying macros for
other sections. However, when I come back to some of the cells that I've
inserted comments for, I notice that the comment field grows, esp if after
i've 'corrected' the size of the comment field, I write a value to the cell.


Is there any way I can fix the size of the comment field so that it will
keep from doing this ? Or do I have to write a custom macro / form to handle
this ?

Thanks,
Chad

Just to add that I've also found this to be a problem. I've used something
similar to Gary's change all comments, but I suspect the my particular "evil
force" coinsides with Autofilter hiding and then unhiding rows (not in every
case but just occasionally!) If you don't have too many comments then you
could run the resize macro after every autofilter, but you'd need to check
the performance hit on this and decide whether its worth it.

Best regards

John


"Gary''s Student" wrote in message
...
Several of my users have complained about similar problems. Comments are
formatted and then some "evil force" changes them. The following is a
technique to un-do the actions of the "evil force":

1. after formatting the comments, create and run a macro to read the
dimensions of each comment and store them in a public array.

2. create another macro to read the public array and apply the dimensions
to
each comment

If the "evil force" attacks, you just run the second macro to re-establish
the dimensions of each comment. The following is an example of how to
read
(or write) comment dimensions:


Sub readum()
Dim c As Comment
For Each c In ActiveSheet.Comments
x = c.Shape.Width
y = c.Shape.Height
MsgBox (x & " by " & y)
Next
End Sub

--
Gary's Student


"ChadF" wrote:

I have a worksheet that has a number of comments scattered all over.

I'm using the default 'Edit Comment' Excel function to build them all.

I set the size manually cropping all the extraneous space so the area of
the
comment is only aruond the text I want.

Thinking that I'm done, I continue doing development, modifying macros
for
other sections. However, when I come back to some of the cells that I've
inserted comments for, I notice that the comment field grows, esp if
after
i've 'corrected' the size of the comment field, I write a value to the
cell.


Is there any way I can fix the size of the comment field so that it will
keep from doing this ? Or do I have to write a custom macro / form to
handle
this ?

Thanks,
Chad

Ok ... so I should write something akin to your detect / store comment sizes
into a public array on a Workbook_Open event and then on a Worksheet_Change
event, do a sanity check to re-size them ??

Sounds like the most painless way to go ... I got something like 30 odd
comments ..



"John" wrote:

Just to add that I've also found this to be a problem. I've used something
similar to Gary's change all comments, but I suspect the my particular "evil
force" coinsides with Autofilter hiding and then unhiding rows (not in every
case but just occasionally!) If you don't have too many comments then you
could run the resize macro after every autofilter, but you'd need to check
the performance hit on this and decide whether its worth it.

Best regards

John


"Gary''s Student" wrote in message
...
Several of my users have complained about similar problems. Comments are
formatted and then some "evil force" changes them. The following is a
technique to un-do the actions of the "evil force":

1. after formatting the comments, create and run a macro to read the
dimensions of each comment and store them in a public array.

2. create another macro to read the public array and apply the dimensions
to
each comment

If the "evil force" attacks, you just run the second macro to re-establish
the dimensions of each comment. The following is an example of how to
read
(or write) comment dimensions:


Sub readum()
Dim c As Comment
For Each c In ActiveSheet.Comments
x = c.Shape.Width
y = c.Shape.Height
MsgBox (x & " by " & y)
Next
End Sub

--
Gary's Student


"ChadF" wrote:

I have a worksheet that has a number of comments scattered all over.

I'm using the default 'Edit Comment' Excel function to build them all.

I set the size manually cropping all the extraneous space so the area of
the
comment is only aruond the text I want.

Thinking that I'm done, I continue doing development, modifying macros
for
other sections. However, when I come back to some of the cells that I've
inserted comments for, I notice that the comment field grows, esp if
after
i've 'corrected' the size of the comment field, I write a value to the
cell.


Is there any way I can fix the size of the comment field so that it will
keep from doing this ? Or do I have to write a custom macro / form to
handle
this ?

Thanks,
Chad

I know this is an old topic, but if someone is tracking down this bug
like I was, you may find it useful.

I've seen the "evil force" activated when adding and/or deleting rows.
Today I found all of my carefully sized comments messed up after
adding some rows.

If using "Freeze Panes", this also affects the appearance of comments
that straddle the frozen line(s).

It looks like I'll be resorting to the "code clamp" - forcibly
restoring comment size and location in code.

FWIW...
tbone