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

 

[ranman256]

why are you using text boxes instead of cells?

 

[Vilep]

Because I want to be able to locate the text other places in an easy way and for the layout options (boxes)

 

[DanteAmor]

Hi @Vilep

I have some doubts.

Are the textboxes you have in your sheet ActiveX controls or Form Controls?
This is important for you to answer, since the development of the macro depends on this answer.

If you put the text in sheet1, cell A1 and press the button. Assuming you have a textbox on sheet5 with the text from cell A1. Then the macro will go to sheet5 and select the textbox. But the button stayed on sheet1. To press the button, you would have to go to sheet1 and press the button, but this would start the process again and select sheet5 again.
That could be fixed, instead of a button, with a shortcut key and running the macro, which would cycle through the sheets, from the current sheet to the next, to find the next value, is that what you want?

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[Vilep]

Hi Dante
A shortcut - rather than a button - will be fine to activate the search (right now - later I would like a button, but my primary concern is not being able to search the text in the boxes).
The boxes are just "basic boxes as shapes" started from a sheet using Insert > Text > Text box.

 

[DanteAmor]

Preferably, the macro can search within the sheet or better within all sheets of the open workbook.
I would like a button

Follow the instructions carefully.

The following macro needs 1 cell and 2 columns that you have available on sheet1 (or the sheet where you want it to start).

In cell A1, on sheet1, write the text to search. Every time you modify cell A1 it will be like a "restart" of the search, it means that the next search will start in the first sheet and in the first textbox. If you don't change cell A1, then the next search will continue where the previous search left off.
You press the button and the macro will search all the sheets and all the textboxes, starting to search with sheet1 and stopping at the first textbox that contains the text.
If you press the button again, the macro will look for the next textbox that contains the text, it may be on the same sheet or the next one.

In the macro the 2 columns that are required to be available are columns AA and AB within sheet1.
You can change the sheet, cell and columns in this part of the macro:

  vle = Sheets("Sheet1").Range("A1").Value
  Set rng = Sheets("Sheet1").Range("AA1")

Put the following code in the events of sheet1.

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

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Put the following code in a module and assign it to a button:

Sub Searching_for_text_in_textboxes()
  Dim sh As Worksheet
  Dim shp As Shape
  Dim vle As Variant
  Dim i As Long, j As Long, k As Long, m As Long, n As Long
  Dim txt As String, ky As String, ShapeName As String
  Dim rng As Range, f As Range
 
  vle = Sheets("Sheet1").Range("A1").Value
  Set rng = Sheets("Sheet1").Range("AA1")
 
  If vle = "" Then
    MsgBox "Enter value in A1"
    Exit Sub
  End If
 
  If rng.Value = "" Then
    For Each sh In Sheets
      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
      Exit Sub
    Else
      rng.Cells(1, 2).Value = "X"
    End If
  End If
 
  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 Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Vilep]

Hi Dante
Thanks very much. It works very nicely searching as wanted! Amazing to see.
When using your code (and me clicking the global button), I realize me wanting:
- Please, a local search within the active sheet as well (so a local button in all my sheets working alongside the global macro).
- Rather more, for the global search, would it be possible for the button to trigger a dialogbox, where I can enter the search text, and then traverse the boxes (as my box in sheet1 disappears moving to other sheets).

Thanks a lot.

 

[DanteAmor]

hanks very much. It works very nicely searching as wanted! Amazing to see.

- Rather more, for the global search, would it be possible for the button to trigger a dialogbox, where I can enter the search text, and then traverse the boxes (as my box in sheet1 disappears moving to other sheets).

For the "GLOBAL" macro you still need cell A1 on sheet1 and now 4 columns.
The code on sheet1 is also necessary.

The operation is as follows:
- Change code of sheet1 for this:

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

- Run the "GLOBAL" macro, the inputbox appears, by default the value of the last search appears. If it is blank, then it captures the initial text to begin the search.
- If you change the text, press OK, then the search will start.
- If you don't change the text, press OK, then it will continue with the next search.
- If you press NO, then the search is cancelled.

Sub Searching_for_text_GLOBAL()
  Dim sh As Worksheet
  Dim shp As Shape
  Dim vle As Variant
  Dim n As Long
  Dim txt As String, ky As String, txtSearch As Variant
  Dim rng As Range, f As Range
  
  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("AA1")
  
  If rng.Value = "" Then
    For Each sh In Sheets
      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
      Exit Sub
    Else
      rng.Cells(1, 2).Value = "X"
    End If
  End If
  
  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 Sub

- Please, a local search within the active sheet as well (so a local button in all my sheets working alongside the global macro).

For the "LOCAL" search, you need cell A1 on sheet1 and 4 columns AA to AD on the same sheet1, same as the "GLOBAL" macro.

The steps are the same as the "GLOBAL" macro, only the "LOCAL" macro will only search the active sheet.

Sub Searching_for_text_Local()
  Dim sh As Worksheet
  Dim shp As Shape
  Dim vle As Variant
  Dim n As Long
  Dim txt As String, ky As String, txtSearch As Variant
  Dim rng As Range, f As Range
  
  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("AC1")
  
  If rng.Value = "" Then
    'For Each sh In Sheets
      Set sh = ActiveSheet
      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
      Exit Sub
    Else
      rng.Cells(1, 2).Value = "X"
    End If
  End If
  
  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 Sub

 

[Vilep]

Again very nice .
The thing with the dialog box is to be able easily to find the right textbox, and the next found may not be the right one. And to initiate next search, I need to go back to the Global button in
sheet1 to continue.
Thus, would it be possible in the dialog box to have a button "Continue" that keeps the dialog box open, and finds the next box?
Thanks a lot!

 

[DanteAmor]

and the next found may not be the right one

I don't know what you're referring to. But check carefully inside the textbox, perhaps the word you are looking for is part of a longer word, for example if you search for "ref", it may be that in the box you have the word "carefully".

Thus, would it be possible in the dialog box to have a button "Continue" that keeps the dialog box open, and finds the next box?

Put the following macro for your LOCAL button:

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

Next for your GLOBAL button.

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

And the next in a module, even all 3 macros can be in the same module.

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("AC1")
   
    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

Keep the code from sheet1, it is not changed.

---------------------------------
Now it works like this:
- Run the LOCAL macro, type some text and press OK. The dialog box will appear again, press OK for next search or CANCEL to stop.
- Same for the GLOBAL macro.

Remember that to start the search, you must change the text in the dialog box.