Searching for text in textboxes in Excel

[Vilep]

Hi

I want to be able to search for written text within textboxes in one or more sheets of the spreadsheet.
I've found the "Search for text within textbox and select text box" but I'm a novice into Excel VBA, and I get errors such a "subscript out of range", when trying to copy...

So I have a number of textboxes with some text in a spreadsheet, and can't use Ctrl-F while searching.
I would like to, e.g. in cell A1, be able to write e.g. Christmas, and then the macro, if clicking on a button, would highlight /mark first box 1 and then again clicking on the button, the next box 4.
Or if writing Christmas evening, then go directly to box 4 as this is the only box with the text.

Preferably, the macro can search within the sheet or better within all sheets of the open workbook.

What to do fully from scratch?

Thanks

 

[Vilep]

Thanks! it's certainly nice with the dialogbox.

Somehow, marking (making the text box found active) is not always working for the global macro. E.g., I've got four boxes in sheet 1 and 6 in sheet 2, and the search result afterwards is fine identifying correct boxes.
But in practice, box 4 in sheet 2 was not marked, and then at next search in progress by clicking ok, then box 5 was marked.

Any idea of why a box may not be marked?

Sheet1|TextBox 1
Sheet1|TextBox 3	X
Sheet1|TextBox 4
Sheet2|TextBox 2
Sheet2|TextBox 4
Sheet2|TextBox 5
Sheet2|TextBox 6

NB: I tried the same yesterday, also with boxes not marked during the search, so today I've verified it.
NBB: I've also tried to delete all text in columns AA:AD and in A1, before rerun, but still with some boxes not marked (but correctly identified in the result list).


Thanks.

 

[DanteAmor]

is not always working for the global macro

I forgot to change the column.

In this macro:
Sub Searching_MAIN(sCol As String, ini As Long, fin As Long)


Change this line:

Set rng = Sheets("Sheet1").Range("AC1")

By this line:

Set rng = Sheets("Sheet1").Range(sCol)

 

[Vilep]

Thanks, but still the search is not marking each identified box

 

[DanteAmor]

In my tests it works for me.
Check that you are entering the code correctly.
Check that they are indeed text boxes.
Try a new book with 2 or 3 controlled examples, once you verify how it works. Try in your original book and identify which text box is not selected, to verify that it actually has the text and that it is one of the text boxes you mentioned.

Do you have hidden or protected sheets?

 

[Vilep]

I've copied the content of "Private Sub Worksheet_Change(ByVal Target As Range)" from "Friday at 5:41 PM" to Sheet1, Sheet2, Sheet3 into window opening when selecting View code at the sheet.
Furthermore, I've copied content of the three "Sub Searching_for_text_LOCAL()", "Sub Searching_for_text_GLOBAL()" and content of "Sub Searching_MAIN(sCol As String, ini As Long, fin As Long)" from "Saturday at 2:23 PM" into same module, and then replaced one line to new "Set rng = Sheets("Sheet1").Range(sCol)" as per "12:40 PM" today.
Next, I created "local" buttons in the three sheets referencing the local macro, and a "global" button referencing the global macro.
And created/copied a number of text boxes to each sheet with/without relevant search text.

Using the dialogbox, I may prior enter new text i sheet1!A1, but enter new text to be searched in the dialogbox. Results are for global button, that one of the text boxes in sheet2 and sheet3 aren't marked (but identified in search results).
For local macros, results are fine in sheet1, but sporadic in sheet2 or sheet3 (eg. when in sheet3 identifying textbox in sheet2, or seemingly not finding a box, but when excaping the dialogbox a text box is marked, and all correct boxes written in sheet1!AC1 and down ).

Should local search results be in local sheets? I suppose as the dialogbox exists, entering text in Sheet1!A1 is "just" for resetting earlier search results?
Maybe local search in e.g. 3 sheets can be a priority for consistent result as results from sheet1 nicely works very fine?

This is very usefull for me.

NB: I do not have any hidden or protected sheets.

Thanks

 

[DanteAmor]

The buttons work wonderfully!!!

I explain the instructions again, you must be careful.

1. Delete all codes and macros from sheets and modules.

2. The following code only on sheet1, NOT on all sheets, only on sheet1.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "A1" Then
    Sheets("Sheet1").Range("AA:AD").ClearContents
  End If
End Sub

3. In a module:

Sub Searching_for_text_LOCAL()
  Call Searching_MAIN("AC1", ActiveSheet.Index, ActiveSheet.Index)
End Sub

Sub Searching_for_text_GLOBAL()
  Call Searching_MAIN("AA1", 1, Sheets.Count)
End Sub

Sub Searching_MAIN(sCol As String, ini As Long, fin As Long)
  Dim sh As Worksheet
  Dim shp As Shape
  Dim vle As Variant
  Dim n As Long, m As Long
  Dim txt As String, ky As String, txtSearch As Variant
  Dim rng As Range, f As Range
  Dim bln As Boolean
 
  Do While True
    n = 0
    bln = True
    vle = Sheets("Sheet1").Range("A1").Value
    txtSearch = InputBox("Write the text to search", , vle)
    If txtSearch = "" Then
      Exit Sub
    End If
  
    If txtSearch <> vle Then
      Sheets("Sheet1").Range("A1").Value = txtSearch
    End If
  
    vle = txtSearch
    Set rng = Sheets("Sheet1").Range(sCol)
  
    If rng.Value = "" Then
      For m = ini To fin
        Set sh = Sheets(m)
        For Each shp In sh.Shapes
          If shp.Type = 17 Then   'TextBox Type
            ky = sh.Name & "|" & shp.Name
            txt = shp.TextFrame.Characters.Text
            If InStr(1, txt, vle, vbTextCompare) > 0 Then
              n = n + 1
              rng.Cells(n, 1).Value = ky
            End If
          End If
        Next
      Next
      If n = 0 Then
        MsgBox "No textbox contains the text: " & vle
        bln = False
      Else
        rng.Cells(1, 2).Value = "X"
        bln = True
      End If
    End If
  
    If bln = True Then
      Set f = rng.Offset(0, 1).EntireColumn.Find("X", , xlValues, xlWhole)
      If Not f Is Nothing Then
        ky = f.Offset(0, -1).Value
        Sheets(Split(ky, "|")(0)).Select
        ActiveSheet.Shapes(Split(ky, "|")(1)).Select
        f.ClearContents
        If f.Offset(1, -1).Value <> "" Then
          f.Offset(1).Value = "X"
        Else
          rng.Cells(1, 2).Value = "X"
        End If
      End If
    End If
  Loop
End Sub

4. In each sheet, you create a button, assign to that button the macro "Searching_for_text_LOCAL".

5. You create a button on sheet1, assign the macro "Searching_for_text_GLOBAL" to this button.


6. Oh and another thing, check that the textboxes are visible on the screen, it may be that the texbox is too low, so when you run the macro it selects the textbox, but it is so low that it does not appear on the screen and when the textbox is active dialog, you cannot scroll down to verify that the box has indeed been selected.

Ex:

Reducing the sheet, you can see that the box is selected: