Comment boxes are shrinking and "disappearing"

P

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.
 
B

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
 
S

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
 
B

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
 
S

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
 
B

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
 
S

Susan

i didn't know it either! i just recorded a quick macro in which i
changed that property & then "stole" the code.
:D
ha ha
did you try it out? does it work?
susan
 
B

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
 
S

Susan

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top