Run-time error '-2147024809 (80070057)': The specific value is out of range RRS feed

  • Question

  • Hi,

    I am new to vba coding and would appreciate any help I could get for the above error.  I have a roll-up heirarchy that show the crediting from one person to another person.  The heirarchy is created using shape  from excel and has employee names and ids in each shape with arrows.  It is shown through shapes (ovals, rectangles etc) which I have put in the text box both the person's name and the employee ids.  I am trying to write a vba code that will allow me to search and find the name or employee id as Ctrl+F will not work on shapes.  The below is the code that I'm using.  The error seems to be coming from this line: sTemp = shp.TextFrame2.TextRange.Characters.Text.  I am currently using excel 2010.  Thank you so much for helping me.

    Sub FindInShape1()
        Dim rStart As Range
        Dim shp As Shape
        Dim sFind As Variant
        Dim sTemp As Variant
        Dim Response

        sFind = InputBox("Search for?")
        If Trim(sFind) = "" Then
            MsgBox "Nothing entered"
            Exit Sub
        End If
        Set rStart = ActiveCell
        For Each shp In ActiveSheet.Shapes
            sTemp = shp.TextFrame2.TextRange.Characters.Text
            If InStr(LCase(sTemp), LCase(sFind)) <> 0 Then
                Response = MsgBox( _
                  prompt:=shp.Name & vbCrLf & _
                  sTemp & vbCrLf & vbCrLf & _
                  "Do you want to continue?", _
                  Buttons:=vbYesNo, Title:="Continue?")
                If Response <> vbYes Then
                    Set rStart = Nothing
                    Exit Sub
                End If
            End If
        MsgBox "No more found"
        Set rStart = Nothing
    End Sub



    • Edited by ygust Wednesday, July 22, 2015 11:06 PM
    Wednesday, July 22, 2015 11:03 PM

All replies

  • Re:  text in a shape

    Your code works for me, as is, in xl2010 on "block arrows".
    You might try...  shp.TextFrame.Characters.Text
    All shapes do not have the same properties.

    Note:  the code will not work on a Textbox or Controls.

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:58 PM
    Thursday, July 23, 2015 12:52 AM
  • Hi Jim,

    Thank you so much for answering my question so quickly.

    When I replace the code with shp.TextFrame.Characters.Text, I get Run-time error '1004' Application

    defined or object defined error.  However, I do have a textbox, but even when I remove it

    I still get the same error.  It would be great if I can keep the textbox.



    • Edited by ygust Thursday, July 23, 2015 3:57 PM
    Thursday, July 23, 2015 2:11 AM
  • Re: shapes with errors

    The usual practice is to identify the type of shape and program accordingly.

    If shp.Type = msoAutoShape Then
       strText = shp.TextFrame.Characters.Text
    ElseIf shp.Type = msoFormControl Then
       'something else
       'something else
    End If

    Try inserting a commented out msgbox to find the problem shape(s).
    Set the VBE to break on all errors.
    Uncomment the msgbox when an error occurs.

    For Each shp In ActiveSheet.Shapes
       ' MsgBox
        sTemp = shp.TextFrame.Characters.Text

    Suggest you edit your last post and remove the large empty space at bottom.
    Jim Cone

    Thursday, July 23, 2015 3:26 AM
  • Hi Jim,

    Thank you for the suggestions.  The problem is that the heirarchy contains line arrows (msoConnectorStraight and text box (msoFormControl) in addition to the ovals/rectangles shapes.  I need this to show the crediting movement.  I would really appreciate your help on how to include this in the coding.



    Thursday, July 23, 2015 6:47 PM
  • Re:  shape types

    I don't understand "crediting movement"
    Confirm that you want to skip connectors as I assume there is no text in them to extract.
    Advise type of Textbox... FormControl or ActiveX.

    For reference...
    Constant             Value

    msoAutoShape        1
    msoCallout              2
    msoChart                3
    msoComment          4
    msoEmbeddedOLEObject    7
    msoFormControl            8
    msoFreeform                5
    msoGroup                     6
    msoLine                        9
    msoLinkedOLEObject     10
    msoLinkedPicture          11
    msoMedia                     16
    msoOLEControlObject    12
    msoPicture                    13
    msoPlaceholder             14
    msoShapeTypeMixed      -2
    msoTextEffect               15
    msoConnectorStraight    1
    Jim Cone
    Thursday, July 23, 2015 7:23 PM
  • Hi Jim,

    Yes, that is exactly what I want to do - skip the connector.  The textbox is msoFormControl.



    Thursday, July 23, 2015 11:25 PM
  • Re:  shapes on sheets

    It has been so long since I have worked with controls on sheets that I hope I don't embarress myself.
    This code structure worked for me with arrows, connectors and both types of text boxes on a worksheet...
      If Shp.Type = msoAutoShape Then
        On Error Resume Next                                  'skips connectors
        strTemp = Shp.TextFrame.Characters.Text
        On Error GoTo 0
      ElseIf Shp.Type = msoTextBox Then
        strTemp = Shp.TextFrame.Characters.Text
      ElseIf Shp.Type = msoOLEControlObject Then    'ActiveX
        strTemp = Shp.Parent.OLEObjects(Shp.Name).Object.Value
      End If

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 7:59 PM
    Friday, July 24, 2015 1:26 AM