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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
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

"Tom" wrote:

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,
It's Excel 2003, the error I'm getting is "cannot shift objects off sheet"
when trying to hide some columns as described he
http://en.allexperts.com/q/Excel-105...ects-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

"FSt1" wrote:

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" wrote:

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