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

[leonlai]

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

 

[Yongle]

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

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

 

[Yongle]

a simple dump to message box

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

 

[leonlai]

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

 

[Yongle]

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

Sub PutTextInShape()
    Dim msg As String, shp As Shape
    msg = "comment1" & vbCr & "comment2" & vbCr & "comment3"
    
    Set shp = ActiveSheet.Shapes("[COLOR=#b22222]Rectangle 1[/COLOR]")
    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)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        With ActiveSheet.Shapes("[COLOR=#b22222]Rectangle 1[/COLOR]")
               .Left = ActiveWindow.VisibleRange(2, 2).Left
       .Top = ActiveWindow.VisibleRange(2, 2).Top
    End With
End Sub

 

[leonlai]

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

 

[Yongle]

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

 

[leonlai]

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

 

[Yongle]

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

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
   [COLOR=#ff0000] Call PutTextInShape[/COLOR]
End Sub

 

[leonlai]

Hi, Yongle

I tested your idea.

It works! Great!

Best Regards,
Leon