Search for text within textbox and select text box

[dpalomino1009]

Hello,

I wanted to search for text within a textbox and select it. I was surprised that excel's search wont find text within a textbox. Is there a macro that could find any given text within a text box and select the text box containing that given text? Maybe have the macro could search for text in a selected cell after entering text in it? Please let me know if you have any ideas.

Thank you,

 

[dpalomino1009]

Almost, alll except the map and they key.

 

[Yongle]

This is the type of thing you need to do - amend the dimensions etc accordingly
List the names of the shapes to be excluded, separated by commas under he first Case statement
Call the procedure from the relevant event macros which must be placed in sheet "Map"
(see post#6 Deactivate and Selection_Change)

Sub LoopShape()
    Dim Shp As Shape
    For Each Shp In Sheets("Map").Shapes
        Select Case Shp.Name
            Case "NameOfMapShape", "NameOfKeyShape"
                'do nothing
            Case Else
                Shp.Height = 99
                Shp.Width = 99
                Shp.TextFrame.Characters.Font.Size = 99
        End Select
    Next Shp
End Sub

 

[dpalomino1009]

Thank you, so close! It's working, but for some reason the map gets formatted as well as the cell that matches the text, even though the map is a picture and has no text. Is there a way to avoid having the map change along with the shape? Noting code below.

Private Sub Worksheet_Change(ByVal Target As Range)
ElseIf Not Intersect(Range("E2:E192,E202:E251"), Target) Is Nothing Then    
    Dim Shp As Shape, Ws As Worksheet
    Set Ws = Sheets("Map")
    
    Ws.Activate: Ws.Range("A1").Select
    
    For Each Shp In Ws.Shapes
        
        If Shp.DrawingObject.Text = Target Then
        Shp.Select
        Shp.Width = 108
        Shp.TextFrame.Characters.Font.Size = 15
        End If
        
    Next Shp
    
End If
Application.ScreenUpdating = True


End Sub

 

[Yongle]

Why have you posted the code which selects the shape? - that was sorted previously

You were trying to reset the shape back to original size
Here is a simpler option for you to return shape to its original size
- Variable Shp is declared as a public variable, making it available to all procedures after being set in Worksheet_Change
- It is a way to allow VBA to remember which shape was selected
- DO NOT declare Shp inside any other procedures - use a different variable name instead

Make the variable Public
1. Amend Worksheet_Change macro and remove the declaration of Shp

Dim [COLOR=#a9a9a9]Shp As Shape, [/COLOR]Ws As Worksheet
[COLOR=#ff0000][I]change to:[/I][/COLOR]
Dim Ws As Worksheet

2. Create a public variable at the top of a STANDARD module
- it must be placed ABOVE all procedures

Public Shp As Shape

Reset the selected shape
Insert these procedures in SHEET module for sheet "Map" (delete previous versions with the same name in that module)

Private Sub Worksheet_Deactivate()
    If Not Shp Is Nothing Then ResetShape
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Shp Is Nothing Then ResetShape
End Sub

Private Sub ResetShape()
    Shp.Width = 50
    Shp.TextFrame.Characters.Font.Size = 10
    Set Shp = Nothing
End Sub