# Thread: On a sheet with many comments, is it possible to spread them out so as not to hide other comments? Thanks:  2 Post #5251798 (1)Post #5252031 (1) Likes:  1 Post #5252031 (1)

1. ## 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. ## 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```

3. ## 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```

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

Hi, Yongle

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

1. Manually insert a rectangle in the worksheet - mine was named "Rectangle 1")
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```

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

Leon

9. ## 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. ## Re: On a sheet with many comments, is it possible to spread them out so as not to hide other comments?

Hi, Yongle

It works! Great!

Best Regards,
Leon

## User Tag List

#### Posting Permissions

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