Finding Shapes in Workbook

[wiredgecko]

I have an Excel workbook with 12 sheets, and in each sheet there are several shapes (lets say rectangles). In each rectangle there is text in it(1234 Smith, 3452 Jones, etc). I've have a search button into which I enter the number, name or both and it should be found. The problem I'm bumping into is that if the shape contains both numbers and letters an error message pops up saying that the shape can't be found. If I only have letters or only numbers I'm able to find the shape.

I would like to be able to put both letters and numbers in the shape and be able to find it whether I search by name or number.

My VBA skills are very poor, so please dumb it down for me

Thanks
Alex

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG16Jun12
[COLOR="Navy"]Dim[/COLOR] Shp [COLOR="Navy"]As[/COLOR] Shape, MyRef [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sht [COLOR="Navy"]As[/COLOR] Worksheet, Sp [COLOR="Navy"]As[/COLOR] Variant
MyRef = InputBox("Enter Number/String ", "Data Input")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Sht [COLOR="Navy"]In[/COLOR] Worksheets
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Shp [COLOR="Navy"]In[/COLOR] Sht.Shapes
    [COLOR="Navy"]If[/COLOR] Shp.Type = msoShapeRectangle [COLOR="Navy"]Then[/COLOR]
            Sp = Split(Shp.TextFrame.Characters.Text, " ")
            [COLOR="Navy"]If[/COLOR] MyRef = Sp(0) Or MyRef = Sp(1) [COLOR="Navy"]Then[/COLOR]
                MsgBox "Your shape is on sheet:- """ & Sht.Name & """ and named " & Shp.Name
                [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Shp
[COLOR="Navy"]Next[/COLOR] Sht
MsgBox "No shape with text " & MyRef & " was found"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick