Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Move and Size comments through VBA

Status
Not open for further replies.

hrm1220

Technical User
Aug 31, 2005
134
US
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
Code:
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
 
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
 
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?
 
ok. well sorry for once again bother you guys. It actually does work. Again thx for looking for me
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top