Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

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

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

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

    Hi,

    I may just be dreaming...

    If there are many comments on a worksheet, and we click a macro button that shows them all, some comments may hide others, so we cannot read them all.

    My comments are programmed to autofit. So, they occupy minimum space. The button acts as a toggle button and can hide or show all comments.

    I am just wondering if it is possible to "spread" the comments, so that one does not hide another one and we can read them all at a glance.

    I googled, but don't know the keyword to search for, and so did not find an answer.

    Any idea or url references will be much appreciated.

    Thanks
    Leon

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    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?

    It is easy to move comments around by adjusting LEFT and TOP properties
    - the trick is in deciding how to adjust!

    Code below loops through all the comments and lays them out in a grid which may not be what you want
    - simply come up with your own rule to determine each comment's LEFT and RIGHT value

    Experiment until you are happy with the results
    - you may want all the comments on the left of the screen etc
    - how the comments are distributed makes a big difference to the "look" because of the connecting lines
    - try not to make your rules overly complicated

    Test on a copy of your workbook

    If you have any questions let me know
    Code:
    Sub CellComments()
        On Error GoTo Handling
        Dim cel As Range, T As Double, L As Double
        For Each cel In ActiveWindow.VisibleRange
            If Not cel.Comment Is Nothing Then
                If L > 800 Then
                    L = 0
                    T = T + 110
                End If
                With cel.Comment.Shape
                    .Top = T
                    .Left = L
                    L = L + .Width * 1.1
                    .Visible = True
                End With
            End If
       Next cel
    Handling:
    
    End Sub
    Last edited by Yongle; Mar 28th, 2019 at 07:29 AM.

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    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?

    a simple dump to message box
    Code:
    Sub ListComments()
        On Error Resume Next
        Const T = vbTab, V = vbCr
        Dim cel As Range, msg As String
        msg = "REF" & T & "VAL" & T & "COMMENT"
        For Each cel In ActiveWindow.VisibleRange
             msg = msg & V & cel.Address(0, 0) & T & cel & T & cel.Comment.Text
        Next
        MsgBox msg, , ""
    End Sub
    Last edited by Yongle; Mar 28th, 2019 at 07:35 AM.

  4. #4
    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

    Thanks a lot for your reply.

    Both your Subs work perfectly.

    Your Sub CellComments is wonderful (from a theoretical point of view), but I don't find it very interesting from a practical point of view, especially if you have many comments.

    We'll also need another macro (I already have one) which will return the comment boxes to their original positions.

    ---

    But your Sub ListComments is something I did not think of, and which will prove extremely useful for my project.

    I have already written a macro which dumps the details of the comments to another worksheet
    But your MsgBox approach is much more useful as the users see the details of the comments on the screen they are working with.

    Just one inconvenience.
    The MsgBox is a modal form, and until it is closed, the user's screen is frozen.

    Is there any way to make the MsgBox non-modal. Or is there an alternative?

    Best Regards,
    Leon

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    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?

    Use a shape instead of meassage box and then you can manipulate as you want

    Do this in a NEW workbook (no need to add any comments - dummy comments included)
    1. Manually insert a rectangle in the worksheet - mine was named "Rectangle 1")
    2. Add the code
    3. Assign Rectangle1_Click to shape to make it non-visible when user clicks on it (right click on shape to assign macro)

    Test like this:
    4. Run PutTextInShape
    5. Click on shape to see it disappear
    6. If it is what you want, then incorporate method into main macro etc

    Place in STANDARD module
    Code:
    Sub PutTextInShape()
        Dim msg As String, shp As Shape
        msg = "comment1" & vbCr & "comment2" & vbCr & "comment3"
        
        Set shp = ActiveSheet.Shapes("Rectangle 1")
        shp.DrawingObject.Text = msg
        shp.Visible = True
    End Sub
    
    Sub Rectangle1_Click()
        ActiveSheet.Shapes(Application.Caller).Visible = False
    End Sub
    Put in SHEET module and shape will follow cursor when scrolling (clicking in any cell moves it around)
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            With ActiveSheet.Shapes("Rectangle 1")
                   .Left = ActiveWindow.VisibleRange(2, 2).Left
           .Top = ActiveWindow.VisibleRange(2, 2).Top
        End With
    End Sub
    Last edited by Yongle; Mar 28th, 2019 at 10:19 AM.

  6. #6
    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?

    Yongle,

    Thanks for the new idea.

    I now have 2 workbooks: Book1, and the new workbook Book2 (where I inserted Rectangle1)

    You have sent me 2 Subs:
    Sub PutTextInShape()
    Sub Worksheet_SelectionChange

    In which workbook/workbooks do you put these codes?

    Leon

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    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?

    The new workbook was for you to test the new stuff without making a mess of the original
    Everything goes in the new workbook and then you bin it after testing
    Apologies if I confused you

  8. #8
    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?

    Yongle,

    I will try your suggestion tomorrow or during the weekend.

    Although I love your idea of dumping the details of comments to a MsgBox, I just found (to my disappointment), that a MsgBox holds very little data, and may not be appropriate if there are dozens of comments on the sheet.

    I hope your rectangle idea will not suffer from this disadvantage!

    Leon

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,867
    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?

    I had sort of anticipated that ...
    For Each cel In ActiveWindow.VisibleRange only incudes cells in the visible window

    But I should have told you to include this line into the macro that moves the shape as you scroll to refresh the text as you scroll
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            With ActiveSheet.Shapes("Rectangle 1")
                   .Left = ActiveWindow.VisibleRange(2, 2).Left
           .Top = ActiveWindow.VisibleRange(2, 2).Top
        End With
        Call PutTextInShape
    End Sub

  10. #10
    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 tested your idea.

    It works! Great!

    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
  •