Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching within comments added to cells
Is it possible to write VBA code that will search inside the comments
attached to a cell in excel for each sheet in the workbook. The number and names of the sheets will constantly be changing. Ideally what I'd like to do is something like: 1. Go to first sheet in workbook 2. for each sheet in workbook search the comments for desired info 3. On finding result return message stating the sheet name and cell that the comment is attached to and the choice to end searching here and go to that cell or continue searching 4. On not finding result return message with simple 'OK' button and end the macro. Thanks for any help/pointers in the right direction. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching within comments added to cells
Hi,
A starter which looks for text "comment" in comments. If found, it makes the comment visible - I don't know how you get the cell address. Sub FindComments() Dim c As Object, ws As Worksheet, resp Dim cFound As Boolean For Each ws In Worksheets ws.Activate cFound = False For Each c In ActiveSheet.Comments If InStr(1, c.Text, "Comment") < 0 Then resp = MsgBox(ws.Name & vbCrLf & vbCrLf & "Do you want to continue search?", vbYesNo, "Search for comments") cFound = True If resp = vbNo Then c.Visible = True Exit Sub End If End If Next c If Not cFound Then MsgBox "No comment found in " & ws.Name End If Next ws End Sub HTH "red6000" wrote: Is it possible to write VBA code that will search inside the comments attached to a cell in excel for each sheet in the workbook. The number and names of the sheets will constantly be changing. Ideally what I'd like to do is something like: 1. Go to first sheet in workbook 2. for each sheet in workbook search the comments for desired info 3. On finding result return message stating the sheet name and cell that the comment is attached to and the choice to end searching here and go to that cell or continue searching 4. On not finding result return message with simple 'OK' button and end the macro. Thanks for any help/pointers in the right direction. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching within comments added to cells
Hi Red6000,
Try: Sub SearchComments(Optional myStr As Variant) Dim blFound As Boolean Dim blNoMatch As Boolean Dim sStr As String Dim StrText As String Dim rng As Range Dim WS As Worksheet Dim rCell As Range Dim res As Long Dim i As Long, j As Long Dim msg As String Dim mybuttons As Long Dim myTitle As String Dim SearchTxt 'As String If IsMissing(myStr) Then SearchTxt = InputBox _ ("Enter text to be found in comments") If StrPtr(SearchTxt) = 0 _ Or Len(SearchTxt) < 1 Then MsgBox "No searchstring entered!", _ vbInformation, "Search Aborted" Exit Sub End If Else SearchTxt = myStr End If i = ActiveWorkbook.Worksheets.Count For Each WS In ActiveWorkbook.Worksheets On Error Resume Next Set rng = WS.Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 If Not rng Is Nothing Then For Each rCell In rng.Cells j = j + 1 sStr = rCell.Comment.Text blFound = InStr(1, sStr, SearchTxt, vbTextCompare) If blFound Then msg = SearchTxt & _ " found in comment at " _ & rCell.Address(0, 0, , 1) If WS.Index < i And j < rng.Cells.Count Then mybuttons = vbYesNo myTitle = "CONTINUE SEARCH?" Else mybuttons = vbInformation myTitle = "Comment Texr Srearch" End If res = MsgBox(msg, mybuttons, myTitle) blNoMatch = True If res = 7 Then Application.Goto rCell Exit Sub End If End If Next rCell End If Set rng = Nothing Next If blNoMatch = False Then MsgBox SearchTxt & " not found in any comments", _ vbInformation, "No Match!" End If End Sub --- Regards, Norman "red6000" wrote in message ... Is it possible to write VBA code that will search inside the comments attached to a cell in excel for each sheet in the workbook. The number and names of the sheets will constantly be changing. Ideally what I'd like to do is something like: 1. Go to first sheet in workbook 2. for each sheet in workbook search the comments for desired info 3. On finding result return message stating the sheet name and cell that the comment is attached to and the choice to end searching here and go to that cell or continue searching 4. On not finding result return message with simple 'OK' button and end the macro. Thanks for any help/pointers in the right direction. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel and searching for a string in a cell where comments have been added | Excel Discussion (Misc queries) | |||
Allow comments to be added to protected worksheet? - Excel 2007 | New Users to Excel | |||
Keep a column of comments "with" pivot table as pivot tables datachanges, causing rows to be added and deleted. | Excel Discussion (Misc queries) | |||
Delete added comments | Excel Discussion (Misc queries) | |||
Added up # of cells containing a specified word | Excel Worksheet Functions |