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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top