Set properties in Excel comments

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have an Excel spreadsheet with a lot of comments that I need to set the
properties for (they're causing an error but I need to keep them). I need to
set the properties of the comments to "move and size with cells" to eliminate
the error I'm getting. Is there a way to set this with VBA or a way to select
all of the comments and change the formatting for them all at once? If you
double-click on a comment and bring up the Format Comment dialog box, then go
to the Properties tab, I need to set the Object positioning to "Move and size
with cells" for about an hundred comments. What's the best way to accomplish
this?

Tom
 
hi
what error are you getting? what version of excel are you using?
i am confused. comments are assigned toa cell and do move with the cell. and
the only thing i can format is the fonts.
i suspect that you may be using shapes and confusing them with comments.

regards
FSt1
 
Hi,
It's Excel 2003, the error I'm getting is "cannot shift objects off sheet"
when trying to hide some columns as described here:
http://en.allexperts.com/q/Excel-1059/shift-objects-sheet.htm
The solution from MS was to change the properties of the objects (comments)
to "move and size with cells" so I was looking for a way to do it with VBA
since there are so many of them on the sheet. I actually found the solution I
was looking for with some more digging at:
http://support.microsoft.com/kb/211769

Sub Test()
Dim s As Shape
On Error Resume Next
For Each s In ActiveSheet.Shapes
s.Placement = xlMoveAndSize
Next
End Sub

thanks anyway though,

Tom
 
Back
Top