Set properties in Excel comments

[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

 

[Don Guillett]

Look here
http://www.contextures.com/xlcomments03.html#Formatted

 

[FSt1]

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

 

[Tom]

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