PDA

View Full Version : Solved: comments object, placement property



TheAntiGates
04-15-2008, 09:44 AM
To fix the "Cannot shift objects off sheet" problem I want to change all of a sheet's comments. I am weak with objects. I know I want to do
.Comment.Shape.Placement = xlMoveAndSize
but my problem is the object to apply this to and the For statement. Can you help?Sub Macro2()
Dim obj As Object
'Dim obj As Comment
On Error Resume Next
For Each obj In ActiveSheet.Shapes
obj.Comment.Shape.Placement = xlMoveAndSize
Next
End Sub

TheAntiGates
04-16-2008, 07:48 AM
Greg Truby solved this for me elsewhere...Dim rngCell As Range

On Error GoTo EndOfTheLine

For Each rngCell In Cells.SpecialCells(xlCellTypeComments)
rngCell.Comment.Shape.Placement = xlMoveAndSize
Next rngCell
EndOfTheLine:
End Sub

lucas
04-16-2008, 07:54 AM
Hi AG, glad you found a solution and thanks for posting it here for others.

If you are posting the same question in other forums would you please post a link to them?

Please read this. (http://www.excelguru.ca/node/7)