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]

thanks for the feedback

 

[My Aswer Is This]

Here is my suggestion:

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

 

[Yongle]

@My Aswer Is This
Nice and simple

 

[leonlai]

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:

For Each c In ActiveSheet.Comments

to:

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

 

[Yongle]

Amend @My Aswer Is This code and name the shape, and call a macro to delete the shape

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
[I][COLOR=#006400]'delete previous shape if it exist[/COLOR][/I]
    Call DeleteShape("MyChosenName")

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 1, 1).Select
[COLOR=#006400]
'name the new shape
   [/COLOR]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

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

Call DeleteShape("MyChosenName")

 

[leonlai]

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

 

[Yongle]

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)

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

 

[My Aswer Is This]

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.

 

[My Aswer Is This]

Here is a answer to your post. Only applies to area selected.

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

 

[leonlai]

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