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

    Trap Add/Edit Comment (ExcelXP)

    I'm having some problems with code that hides columns. This is caused by comments that do not have the "Move and size with cells" set. My problem is that I can fix all that, -even via VBA- ,but if my users add a new comment to a cell, it seems that -by default- this is NOT using the "Move and size with cells" setting and THUS my problem will re-appear...

    Is there a way to TRAP a user when adding and editing a comment to a cell??? I tried the worksheet.change event, but that doesn't trigger....

    Appreciate any suggestions...

    Erik Jan

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Trap Add/Edit Comment (ExcelXP)

    I do not think there is a way to trap either event. What you could do, it before you run your code that hides columns, make sure all the comments are set to move and size with something like the code attached. This will do it for all worksheets, but you could just do it on the sheet your code will work on.

    Steve

    <pre>Sub AllCommentsMoveSize()
    Dim wks As Worksheet
    Dim cmt As Comment

    For Each wks In Worksheets
    For Each cmt In wks.Comments
    cmt.Shape.Placement = xlMoveAndSize
    Next
    Next
    End Sub</pre>


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

    Re: Trap Add/Edit Comment (ExcelXP)

    Did that.. works! 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
  •