How to determine quickly what cell a comment is from

[sadams1]

I have a spreadsheet with many columns & rows & many comments. I can be working in one part of a sheet & a random comment/note shows up & it's from a cell in some other part. I can manually track it down to hide it but that seems like a waste. Is there some way to determine quickly what cell a comment is from? The "name box" shows "comment 10000" which is little help.

 

[DanteAmor]

Maybe the following can help you:
With this macro you can organize the comments, to put them near the cell to which they belong:

Sub rearrange_comments()
  Dim c As Range
  
  For Each c In Cells.SpecialCells(xlCellTypeComments)
    c.Comment.Shape.Top = c.Top + 2
    c.Comment.Shape.Left = c.Left + c.Width + 2
    c.Comment.Shape.Placement = xlFreeFloating
  Next
End Sub

You can then hide the comment or hide all and show all with

1. Go to the Review tab
2. Click Show/Hide All Comments

 

[Logit]

This macro will display in a Message Box all of the comments with the term you select/enter.

Sub SearchCommentsOnCurrentWorksheet()

    Dim c As Comment

    Dim SearchFor As Variant

    Dim TxtToSearch As Variant

    Dim Ctr As Integer

    Dim Msg As Variant

    SearchFor = LCase(InputBox(Prompt:="Text to find in comments?"))

    Application.DisplayCommentIndicator = xlCommentIndicatorOnly

    Ctr = 0

    For Each c In ActiveSheet.Comments

        TxtToSearch = LCase(c.Text)

        If InStr(1, TxtToSearch, SearchFor) > 0 Then

            c.Visible = True

            c.Parent.Select

            Ctr = Ctr + 1

            If Ctr = 1 Then

                Msg = " comments found:" & vbLf & c.Parent.Address

            Else

                Msg = Msg & vbLf & c.Parent.Address

            End If

        End If

    Next c

    If Ctr = 0 Then

        MsgBox TxtToSearch & " not found in current worksheet comments."

    Else

        MsgBox Ctr & Msg

    End If

End Sub

The following macro will close all open comments :

Sub CloseAllComments()
    Dim ws As Worksheet
    Dim cmt As Comment

    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each comment in the worksheet
        For Each cmt In ws.Comments
            ' Hide the comment
            cmt.Visible = False
        Next cmt
    Next ws

    MsgBox "All comments have been closed.", vbInformation
End Sub

 

[sadams1]

Maybe the following can help you:
With this macro you can organize the comments, to put them near the cell to which they belong:

Sub rearrange_comments()
  Dim c As Range
 
  For Each c In Cells.SpecialCells(xlCellTypeComments)
    c.Comment.Shape.Top = c.Top + 2
    c.Comment.Shape.Left = c.Left + c.Width + 2
    c.Comment.Shape.Placement = xlFreeFloating
  Next
End Sub

You can then hide the comment or hide all and show all with

1. Go to the Review tab
2. Click Show/Hide All Comments

Excellent! been messing with this & it is useful! Greatly appreciated!

 

[sadams1]

This macro will display in a Message Box all of the comments with the term you select/enter.

Sub SearchCommentsOnCurrentWorksheet()

    Dim c As Comment

    Dim SearchFor As Variant

    Dim TxtToSearch As Variant

    Dim Ctr As Integer

    Dim Msg As Variant

    SearchFor = LCase(InputBox(Prompt:="Text to find in comments?"))

    Application.DisplayCommentIndicator = xlCommentIndicatorOnly

    Ctr = 0

    For Each c In ActiveSheet.Comments

        TxtToSearch = LCase(c.Text)

        If InStr(1, TxtToSearch, SearchFor) > 0 Then

            c.Visible = True

            c.Parent.Select

            Ctr = Ctr + 1

            If Ctr = 1 Then

                Msg = " comments found:" & vbLf & c.Parent.Address

            Else

                Msg = Msg & vbLf & c.Parent.Address

            End If

        End If

    Next c

    If Ctr = 0 Then

        MsgBox TxtToSearch & " not found in current worksheet comments."

    Else

        MsgBox Ctr & Msg

    End If

End Sub

The following macro will close all open comments :

Sub CloseAllComments()
    Dim ws As Worksheet
    Dim cmt As Comment

    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each comment in the worksheet
        For Each cmt In ws.Comments
            ' Hide the comment
            cmt.Visible = False
        Next cmt
    Next ws

    MsgBox "All comments have been closed.", vbInformation
End Sub

SWEET! will chew on this some more but looks like this will come in handy. Many thanks!

 

[DanteAmor]

Excellent! been messing with this & it is useful! Greatly appreciated!

Glad to help you, thanks for follow up.

 

[Logit]

Happy to assist. Merry Christmas.