Comment boxes are shrinking and "disappearing"

[PaladinWhite]

I haven't figured out what action causes it to happen yet, but I have a bunch
of comment boxes that look like they're disappearing. They're not actually
going away; the red indicator is still there, and when I mouse-over, one of
two things happens: either the comment box has been shrunk until only half
(or less) of the comment is visible, or it has been shrunk out of sight
entirely, so only the arrow is visible.

If I go to Edit Comment, I can re-enlarge the box back to normal size, and
everything is fine.

The problem described at
http://www.eggheadcafe.com/software/aspnet/30967171/comments-fields-disappear.aspx seems to be the same, if that helps.

 

[Billy Liddel]

Hi try this macro and then save the book.

Press F11, Insert Module and copy the code.

Press ALT + Q to return to Excel. CHoose Tools, Macros, Select the macro and
click Run.

Peter

 

[Susan]

peter didn't show any code in his post to try...............

my idea is - are you inserting rows and/or columns? that will make
the comment boxes stretch and move. if this is the case, left click
on the outline of the comment box. then right click and choose
"format comment." then look under "properties" and choose "don't move
or size with cells."

it's possible peter's non-existent code was written to go through each
comment box and perform this very task.
hope it helps!
susan

 

[Billy Liddel]

Whoops!

No my code just makes the comments visible but good idea Susan. The cose
should have been:

Sub test()
Set cmt = ActiveSheet.Comments
On Error Resume Next
For Each c In cmt
c.Visible = True
Next
End Sub

Regards
Peter

 

[Susan]

the coding to make it "don't move or size with cells" would be:

Sub test()
Set cmt = ActiveSheet.Comments
On Error Resume Next
For Each c In cmt

With c
.Placement = xlFreeFloating
End With

Next
End Sub

you could combine them.................

With c
..Placement = xlFreeFloating
..Visible = True
End With

warning! not tested!
susan

 

[Billy Liddel]

Nice one Susan - I did not know that. I have included your code in the
following revised code that resizes each comment.

Note: the area with comments must be select first.

Sub test2()
'Select area with comments
'before running code
Set cmt = ActiveSheet.Comments
On Error Resume Next
For Each c In Selection
With c
c.Visible = True
.Comment.Shape.Select
Selection.AutoSize = True
.Placement = xlFreeFloating 'By Susan
End With
Next
End Sub

 

[Susan]

i didn't know it either! i just recorded a quick macro in which i
changed that property & then "stole" the code.

ha ha
did you try it out? does it work?
susan

 

[Billy Liddel]

Yes it works but I'm not sure that I'd use it. When you resize the column
(enlarge it) the comment stays where it is and fills the middle of the cell.

Better might be

With Selection
.Placement = xlMove
.PrintObject = True 'or False as required
End With

This was recorded too

Peter

 

[Susan]

hmmmmm. ok! thanks for the feedback
i wonder if the original poster tried it..................

susan