Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing comment text in VBA (ExcelXP)

    Have some large sheets that sometimes have cells with comments that are too wide. As many columns are hidden as well I sometimes get error messages indicating that objects cannot be moved off the sheet.
    I tried several things but in the end the right thing is to ensure the cell comments are formatted right in the first place. To do that I read the comment text into a variable and in a litte sub add some LF's every 120 characters.
    All of this works fine, but I ran into a little problem when I implemented this into my larger code-loop... here's how I wanted it:

    For Each oCmt In ActiveSheet.Comments
    Set oCell = oCmt.Parent
    '
    oCmtTxt = Cleanup_Comment_Text(oCmt.Text) 'cut-off texts at 120-wide
    oCell.ClearComments
    oCell.AddComment oCmtTxt
    With oCmt.Shape
    etc.

    So I start to set up a loop to cycle over all comments in the sheet. Then I process each comment-text in my sub (OK, its a function of course: Cleanup_Comment_Text).
    Now the problem started when I found out that a comment-text is read-only. Therefore, still inside my loop, I now have to first delete the comment (the ClearComments property) and then re-write the text using the AddComment method.
    All fine (and it works); however next a new loop continues inside the larger loop where I did some comment formatting. The problem is that referring to "Ocmt" in the line "With oCmt.Shape" gives an error because I just changed that collection of comments (by removing one and creating a new one).

    Of course a workaround would be to let the large outer loop run for each CELL in the sheet maybe but that would seem such a waste... any suggestions as to how I might be able to fix this loop?

    Thanks,

    Erik Jan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing comment text in VBA (ExcelXP)

    You can usually avoid such problems by looping backwards through the items

    Dim i As Long
    For i = ActiveSheet.Comments.Count To 1 Step -1
    Set oCmt = ActiveSheet.Comments(i)
    Set oCell = oCmt.Parent
    ...
    Next i

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing comment text in VBA (ExcelXP)

    Are you sure??? I'm actually breaking the collection as inside the loop I'm deleting a member and creating a new one. Note also that the inner-loop what the problem occurs STILL intends to act on the current (just re-newed) comment.

    Erik Jan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing comment text in VBA (ExcelXP)

    Instead of referring to oCmt after deleting it, try

    With oCell.AddComment(oCmtTxt).Shape

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing comment text in VBA (ExcelXP)

    That helped me.. thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •