Search Text or Value in Comments Box

R2

New Member
Joined
Oct 8, 2002
Messages
5
How to search a text or value in comments box?

Appreciate any help.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

R2

New Member
Joined
Oct 8, 2002
Messages
5
Thank you for the reply.
I'm sorry I forgot to mention I'm an intermediate Excel user still learning how to code. How do you mean "For each of shapes"?
This message was edited by R2 on 2002-10-09 05:48
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hello,

This macro will search each comment on the active sheet for the text entered by the user. It currently only works on the active sheet but could be modified to work in all sheets, or even in all workbooks.

Let me know how you get on.

Code:
Sub FindTextInComments()
Dim strTextToFind As String
Dim cmt As Comment
Dim blnFound As Boolean

On Error GoTo Bail

strTextToFind = InputBox("Find what?", "Search for text in comments")

If strTextToFind = "" Then Exit Sub 'User pressed cancel

'Now search each comment on the active sheet for the text
'specified for the user.

For Each cmt In ActiveSheet.Comments
    If InStr(1, cmt.Text, strTextToFind, vbTextCompare) > 0 Then
        blnFound = True
        cmt.Parent.Select
        cmt.Visible = True
        MsgBox "Cell : " & cmt.Parent.Address & vbCrLf & _
                "Value : " & cmt.Text, vbInformation, Chr(34) & strTextToFind & Chr(34) & " found"
        cmt.Visible = False
    End If
Next cmt

If blnFound = False Then MsgBox "The search text was not found on this sheet.", vbInformation, "No match found"

Bail:
If Err.Number <> 0 Then MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,128,130
Messages
5,628,871
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top