"Finding" in text boxes

Ivo75

Board Regular
Joined
Feb 7, 2006
Messages
63
Hello everyone,

I have a couple of excelfiles in which someone made a flowchart with text boxes. I would like a search code that would help me find a text or number in those text boxes. The CTRL+F function only works on cells. Does something similar exist for text boxes? Or can this be made in a macro?

Kind regards,

Ivo
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi This will find Values/Text (Not multi values/box) entered in code "InputBox" for all (Drawing) Text boxes on Activesheet.

Code:
Dim mt, AllDat, Shp As Shape, c
Dim oDat As String
c = 0
On Error Resume Next
 oDat = Application.InputBox(prompt:="Please Enter Text/Value to Find ", Title:="Text Box Find", Type:=2)

For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoTextBox Then
        With ActiveSheet.Shapes(Shp.name).TextFrame
            mt = .Characters.Count
    
          If InStr(1, .Characters(1, mt).text, oDat, vbTextCompare)  Then
                AllDat = AllDat & Shp.name & "  At Position " _
                     & InStr(1, .Characters(1, mt).text, oDat, vbTextCompare) _
                        & Chr(10)
                           c = c + 1
                   End If
      End With
   End If
Next Shp

If c > 0 Then
    MsgBox "The Value/Text """ & oDat & """ was found in :-" & Chr(10) _
        & AllDat
Else
    MsgBox "No Matches Found"
End If
Regards Mick
 
Upvote 0
Mike:
I tested your fine macro, but can't get it to perform. I set up 3 textboxes using the Controls Toolbox (Active X) directly on my Sheet1, and also 1 commandbutton to run the macro:

Stepping thru (the code) I notice the line

If Shp.Type = msoTextBox Then (the Shp.type = 12 and the msoTextBox = 17)

My guess is this is the problem. Can you share any observations?
 
Upvote 0
Thx,

The code works for me :).

Jim, the tekstboxes that were used in my sheets are from the drawingbar, ther are referred to in VBA as "Text Box #". The one from the controls toolbox is reffered to as "TextBox#". So one with spaces and the other without.
 
Upvote 0
Jim, i just tried with the controls toolbox version. And indeed it doesn't work for that one. You probably know more about this then I do but maybe this wil point you where to look :)

Thank you guys again
 
Upvote 0
Thanks, but not sure what "part or parts" of code to change here,,,
Mike -- can you assist?
Jim
 
Upvote 0
I just changed

With ActiveSheet.Shapes(Shp.name).TextFrame"

into

With ActiveSheet.Shapes(Shp.Name).TextBox

That seems to work partially. It finds the string, but doesnt return the coordinations.
 
Upvote 0
Hi Jim,
This code will only work for "Text Boxes" from the "Drawing" ToolBar.
From the ToolBar, click "Tools", "Customise" and from the selection shown, place a tick in the "Drawing" Box.
If you want it to work for ActiveX "TextBoxes" the code will need a rewrite.
Regards Mick
 
Last edited:
Upvote 0
Forms-type, Active-X-type, and Drawing-type.....

it's worse thatn

Lions, Tigers and Bears

All (treated) so different. Grrrr
 
Upvote 0
Hi Jim, I know !!
All the different Properties & Methods,OLEObjects,Shapes, FormsControl etc
I find it a real Bowl of Spaghetti.
Still, that matches some of my coding !

Regards Mick
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

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