Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 32

Thread: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

  1. #11
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,907
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    thanks for the feedback

  2. #12
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,814
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Here is my suggestion:
    Code:
    Sub Comments()
    'Modified  3/29/2019  4:58:39 AM  EDT
    Dim c As Comment
    Dim ans As String
    For Each c In ActiveSheet.Comments
        ans = ans & c.Text & vbNewLine
    Next
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Select
    With Selection.ShapeRange.TextFrame2
            .TextRange.Font.Size = 16
            .TextRange.ParagraphFormat.Alignment = msoAlignCenter
            .TextRange.Characters.Text = ans
            .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            .TextRange.Font.Bold = True
    End With
    With Selection
        .AutoSize = msoAutoSizeShapeToFitText
        .Left = ActiveCell.Left
        .Top = ActiveCell.Top
        .ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 255)
    End With
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #13
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,907
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    @My Aswer Is This
    Nice and simple

  4. #14
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Hi, My Aswer Is This

    Thanks for your reply, and your solution is surprisingly unexpected.

    I have tested it, and it works wonderfully.
    With some modification, it will serve my project well.

    The modifications I would like to make are:

    (a) I don't want to list ALL the comments on the sheet. There are too many and I have another Sub to do this.
    Rather, if in a small area, there are too many comments which overlap, I want to select that area, and it is only that small area (Selection) which will be listed.


    I tried to modify your line:
    Code:
    For Each c In ActiveSheet.Comments
    to:
    Code:
    For Each c In Selection.Comments
    but this is not good.


    (b) When the user has read the comments in his chosen range, I want him to be able to click a button which will delete that Shape. What is the ID of that shape? What macro can delete that object?


    Best Regards,
    Leon
    Last edited by leonlai; Mar 29th, 2019 at 10:55 AM.

  5. #15
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,907
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Amend @My Aswer Is This code and name the shape, and call a macro to delete the shape
    Code:
    Sub Comments()
    'Modified  3/29/2019  4:58:39 AM  EDT
    Dim c As Comment
    Dim ans As String
    For Each c In ActiveSheet.Comments
        ans = ans & c.Text & vbNewLine
    Next
    'delete previous shape if it exist
        Call DeleteShape("MyChosenName")
    
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Select
    
    'name the new shape
       Selection.Name = "MyChosenName"
    
    With Selection.ShapeRange.TextFrame2
            .TextRange.Font.Size = 16
            .TextRange.ParagraphFormat.Alignment = msoAlignCenter
            .TextRange.Characters.Text = ans
            .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            .TextRange.Font.Bold = True
    End With
    With Selection
        .AutoSize = msoAutoSizeShapeToFitText
        .Left = ActiveCell.Left
        .Top = ActiveCell.Top
        .ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 255)
    End With
    End Sub
    macro to delete shape
    Code:
    Sub DeleteShape(ShapeName As String)
        On Error Resume Next
            ActiveSheet.Shapes(ShapeName).Delete
    End Sub

    Code behind button to delete the shape at time of user's choosing
    Code:
    Call DeleteShape("MyChosenName")
    Last edited by Yongle; Mar 29th, 2019 at 12:00 PM.

  6. #16
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Hi, Yongle

    I will try your suggestion tomorrow morning.

    I am stuck with part (a) of my question. I don't want the macro to list every comment in the worksheet, but only the few comments that are so crowded together as to be difficult to read. So, I select them, and the macro will list only the selected comments.

    Maybe you can help here.

    Best Regards,
    Leon

  7. #17
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,907
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    You could loop differently and only loop comments that are visible to the user
    - but you will need to amend the code slightly (see previous posts on this thread)

    Code:
        For Each cel In ActiveWindow.VisibleRange
            If Not cel.Comment Is Nothing Then

  8. #18
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,814
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    I always thought of comments as a way to tell a user what a particular cell value might be for.

    Let's say Range("A1").value equals Sum of Range("C1:C40")

    So then I might want a comment in Range("A1") to say something like "Sum Range("C1:C40")


    But if you have hundreds of comments that are visible always and now some are over lapping others.

    And now you want the comments that are over lapping others put into a shape which is located some place.

    I don't see how this helps. The comment is still in the cell and is also now in a shape located some place

    But hey that's just my thoughts.
    Last edited by My Aswer Is This; Mar 29th, 2019 at 12:53 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  9. #19
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,814
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Here is a answer to your post. Only applies to area selected.
    Code:
    Sub Check_For_Comment()
    'Modified  3/29/2019  2:08:51 PM  EDT
    Dim r As Range
    Dim ans As String
    Dim DeleteMe As String
    DeleteMe = "To Delete Me:" & vbNewLine & "Select Me then  Press Delete Key"
    For Each r In Selection
        If Not r.Comment Is Nothing Then ans = ans & r.Comment.Text & vbNewLine
    Next
    If ans = "" Then MsgBox "No Comments found in selection": Exit Sub
    ans = ans & vbNewLine & DeleteMe
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Select
    With Selection.ShapeRange.TextFrame2
            .TextRange.Font.Size = 16
            .TextRange.ParagraphFormat.Alignment = msoAlignCenter
            .TextRange.Characters.Text = ans
            .TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            .TextRange.Font.Bold = True
    End With
    With Selection
        .Left = ActiveCell.Left
        .Top = ActiveCell.Top
        .ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 255)
        .AutoSize = msoAutoSizeShapeToFitText
    End With
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #20
    Board Regular
    Join Date
    Mar 2019
    Location
    Republic of Mauritius
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

    Hi, My Aswer Is This

    I don't see how this helps.
    The following may help you understand what I am trying to achieve.

    You may have noticed that I have asked numerous questions about Comments.

    I am developing an Excel add-in using Add-In Express (this is a wonderful software!), and VB .NET for my company.

    We buy numerous goods (from numerous suppliers) at the request of (numerous) overseas clients.
    We then deliver the goods (at one go, or piecemeal) to the client.
    Then we invoice the client (at one go, or piecemeal).

    The follow up of client's orders is a nightmare, and our staff literally fill their Excel sheets with numerous Comments like:
    "Supplier X underdelivered 5 cans of oil", "We have only 100 kg meat left. Ask client if we need to purchase the difference". etc

    So, I am developing my add-in with Comments being a major consideration.

    It must be very easy for our staff to delete, view, add, display, hide, follow-up , etc... comments.

    ----------

    The code you have given me below is wonderful!

    If our staff is looking at part of out (giant!) worksheet, and finds there are several comments (each hiding another), he will just click a button on the Excel Ribbon, and lo! all the selected comments are readable! No need to waste time prying the entangled comments apart to read them.


    Best Regards,
    Leon

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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