Move and Size comments through VBA

[hrm1220]

I'm trying to fix the "Cannot Shift Objects off sheet" by looping through all the cells in a workbook. If the cell has a comment I want to change the properties of the comment to "Move and Size" (xlMoveAndSize) with the cell instead of the "Dont move or Size with cells" (xlFreeFloating).
I found some code to help loop through comments, but my code for the properties doesn't seem to be working

Sub AdjustComments()
Dim endcol, endrow, x, i, y, r As Range

Application.DisplayCommentIndicator = xlCommentAndIndicator

    For Each r In ActiveSheet.UsedRange
        On Error Resume Next
        x = r.Comment.Text
        If Err = 0 Then
    
        r.commment.Shape.Select True
            With Selection
             [b].Placement = xlMoveAndSize[/b]
            End With
        End If
    Next r
 Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

 

[PHV]

Anyway, when you play with the On Error Resume Next instruction you have to use the Err.Clear method somewhere.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hrm1220]

thx I've added the Err.Clear, but I'm still having problems with the .Placement=xlMoveAndSize
The code is not selecting this for the comments properties. Can you point me in the direction on where I messed up the code?

 

[hrm1220]

ok. well sorry for once again bother you guys. It actually does work. Again thx for looking for me