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,

 

[Yongle]

This works for textbox inserted via Insert Tab \ Text \ TextBox

    With ActiveSheet.Shapes("TextBox 1")
        If InStr(.TextFrame.Characters.Text, ActiveCell) > 0 Then .Select
    End With

 

[dpalomino1009]

Thank you, that will select the textbox with text, but I wanted to search for different text, like "John" or "Tony" for example, within multiple text boxes and have Excel select the one with the text I search for. Ideally it would be triggered after clicking on a cell. For example, in range A1:A20, each cell is referenced to a text box on another sheet. I want to know if I can click on a cell, A3 for example, and have Excel select the corresponding text box with the text that is on A3. Note I have the text box = A3, and I want to go from A3 to the text box by clicking on it. I tried hyperlink but it doesn't let me do it to a text box, only to a cell. Is this something that Excel could do? (Also the text boxes or objects are labeled Rounded Rectangle 1, etc, in my sheet)

 

[Yongle]

Thanks for clarifying what you wanted - that is better for both of us than me trying to guess
Code below assumes that all shapes are in ONE sheet ( ShapeSheetName )

1. Put the code into the SHEET module of the sheet containing the range of cells to be clicked on
( right-click on sheet tab \ select View Code \ paste code below into code window )

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then
        Exit Sub
    ElseIf Not Intersect(Range("A1:A20"), Target) Is Nothing Then
        Dim Shp As Shape, Ws As Worksheet
        Set Ws = Sheets("[I][COLOR=#ff0000]ShapeSheetName[/COLOR][/I]")
        Ws.Activate: Ws.Range("A1").Select
        For Each Shp In Ws.Shapes
            If Shp.DrawingObject.Text = Target Then
                Shp.Select
                End
            End If
        Next Shp
    End If
End Sub

2. Amend ShapeSheetName

3. Test by clicking the cells in range A1:A20

 

[dpalomino1009]

That worked out, thank you!

I also wanted to know if there was a way to zoom in on the selected shape. I tried ActiveShp.Zoom = 100, but it didn't work. Please let me know if you have any ideas. Noting the edited code bellow.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then        Exit Sub
    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
[B]                ActiveShp.Zoom = 100[/B]
                End If
        Next Shp
    End If
End Sub

 

[Yongle]

Zoom the window, not the shape

after

Shp.Select

insert

ActiveWindow.Zoom = 200      ' = desired zoom when selecting shape

To return to normal zoom ....

Place code in SHEET module of Sheet "Map"

... when a cell is selected

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveWindow.Zoom = 75      ' = desired normal zoom for the sheet
End Sub

... when sheet is deactivated

Private Sub Worksheet_Deactivate()
    ActiveWindow.Zoom = 75      ' = desired normal zoom for the sheet
End Sub

For info
The line below was included in the code to END the procedure when the correct shape had been found
Omitting it should not cause a problem but it seems pointless to continue searching ALL shapes after required shape has been found and selected

End

 

[dpalomino1009]

Thank you, but I ran into some issues. The zoom part works but it does not zoom on the selected shape. Is there a way to zoom to the selected shape? Also when I added End before, the code did not select the cell with the matching text, not sure why. Are there other ways to do it effectively?

 

[Yongle]

Zooming applies to the whole window

You could achieve the effect of zooming the shape by temporarily increasing its dimensions (when shape is selected) and resetting it afterwards
(in example below assume original shape has Height 80 and Width 100)

increase by 20%

Shp.Height = Shp.Height  * 1.2
Shp.Width = Shp.Width * 1.2

or specify the size (I would use this method)

Shp.Height = 96
Shp.Width = 120

revert back

Shp.Height = Shp.Height / 1.2
Shp.Width = Shp.Width / 1.2

or

Shp.Height = 80
Shp.Width = 100

font size can be amended like this

Shp.TextFrame.Characters.Font.Size = 20

Note
I would opt for specifying the size - Why?
If (for any reason) the shape is not auto-reverted, shape would increase by a further 20% next time selected etc
Specifying the size (up and down) prevents that becoming an issue

 

[dpalomino1009]

That’s genius! But I ran into another problem. How do I restore the cell to its original size when deselected? Or to trigger back to regular size when the shape is deselected?

 

[Yongle]

are all shapes in Map identical except for content?
- if so, it would be simplest to loop all shapes and set everything to a default size etc

let me know