2

I've come to realize the Excel "Find and Replace" dialog couldn't find text in the textboxes. I think I need a macro to solve this.

How could I find text in the textboxes:

  1. in the active sheet
  2. in all sheets

Thank you.

Here's a sample image to show what I mean by textbox: Image of a textbox in Excel

Improve this question
0

Assuming you only use text fields (and no other Objects) this code should work for you (I didn't try it on Excel 2016 though):

Sub Search()
    Dim shaShape As Shape
    For Each shaShape In ActiveSheet.Shapes
        If shaShape.DrawingObject.Text Like "*all*" Then  '<== specify you're search term
            shaShape.Select
            Exit For
        End If
    Next shaShape
End Sub
Improve this answer
  • It works but with some issues. It will only located to the first match. What if I want to keep searching to see if there is any other matches? Thank you. – masterton Oct 18 '18 at 7:16
  • what do you mean? do you have an example? – Albin Oct 18 '18 at 17:04
  • Just like the "Find" function in the excel, it will go to the next match if I press the "Find next". However the script does not have such a function. Let's say there are 10 textboxes in all worksheets, 3 of them contain the word "all". The script only goes to the first match. There is no way to "find next" and goes to the other matches. I hope it is clear now. Thank you. – masterton Oct 19 '18 at 11:40
  • @masterton You will have to modify the code, how much do you know about VBA? – Albin Oct 20 '18 at 14:34
  • Next to nothing. I don't know how to code. Sometimes I just guess what the macro is trying to do and tweak a bit by trial and error. – masterton Oct 21 '18 at 4:15
0

You can check another solution here.

It exports all the shapes (including textboxes) to a new Word document.

In Word the search function DOES work in textboxes and the problem is solved.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.