+ Reply to Thread
Results 1 to 12 of 12

Find out if Shape has Text

  1. #1
    Registered User
    Join Date
    08-04-2009
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Find out if Shape has Text

    Have an .xls file with multiple sheets and many objects (Shapes) on of those sheets.
    The objects are all rectangles, some of which contain text frames, where I had entered manually some text.

    Also, each shape has a name.

    Now, I want my VBA Macro to take a specific shape (using its name), and find out if it has text frame in it.

    i thought the way to do it is

    Please Login or Register  to view this content.
    ,where X is the string that is the name of the shape I am testing.

    Every example I could find had that type of code above.
    But in my case I get "Object doesn't support this property or method" when I call this code.

    Also, scanning through Shapes Collection properties, I don't actually see "HasTextFrame" mentioned. Only "HasDiagram".

    Would anyone have any suggestions?

    Thanks,
    a1
    Last edited by Andy Pope; 08-06-2009 at 09:53 AM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find out if Shape has Text

    Everything I've read about that property indicates it is a PowerPoint function.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Find out if Shape has Text

    Not sure, but you could try using the AlternativeText property. It appears by default to be populated with any text in the shape, but I don't know whether this is a hard and fast rule.
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    02-19-2005
    Location
    Gurgaon,India
    MS-Off Ver
    2007,2010,2013
    Posts
    180

    Re: Find out if Shape has Text

    May be...

    Please Login or Register  to view this content.
    Kris

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,945

    Re: Find out if Shape has Text

    Looks like the .Characters approach will only work if the shape has had text edited within it, even if it now contains no text.

    So the best approach would be a function that use the character count method but also contains error trapping
    Cheers
    Andy
    www.andypope.info

  6. #6
    Forum Contributor
    Join Date
    02-19-2005
    Location
    Gurgaon,India
    MS-Off Ver
    2007,2010,2013
    Posts
    180

    Re: Find out if Shape has Text

    Thanks Andy for the input. So may be function like this..

    Please Login or Register  to view this content.
    would work

  7. #7
    Registered User
    Join Date
    08-04-2009
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Find out if Shape has Text

    Thanks to all the replies.

    I could get StephenR's to work.

    When i tried Krishnakumar's
    Please Login or Register  to view this content.
    I couldn't get it to work, but maybe I did something wrong.
    As a test, I stuck it into a separate Module in my View Code window.
    Then, I made a test Sub Macro2() to call it.
    i.e.
    Please Login or Register  to view this content.

    But when I tried running it, I always got "Rectangle 168 has no text", even if I explicitly entered some text there.

    Since VBA is far from being my second language, I am sure I am doing something wrong here.

    Thanks,
    a1

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Find out if Shape has Text

    Change the code to:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  9. #9
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Find out if Shape has Text

    That doesn't appear to get round the deleted text point made by Andy.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Find out if Shape has Text

    I've used:
    Please Login or Register  to view this content.
    before.

  11. #11
    Registered User
    Join Date
    08-04-2009
    Location
    North Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Find out if Shape has Text

    Ok, just to report:
    thanks, Romperstomper, for pointing out that I was sending a STRING! to the HASTEXTINSHAPE. That was silly of me.
    So, I did change the code to this:

    Please Login or Register  to view this content.
    and everything worked correctly.

    If I make a fresh shape, and execute the Macro, it has no text, and so the HASTEXTINSHAPE returns "no text". If I Add Text to it, even without typing any characters in the box, it return "there is text".

    Obviously, if I have Alternative Text associated with that shape, this function is not affected.

    Interestingly enough, when I tried to declare shpObj "as Shape" inside HASTEXTINSHAPE, it didn't like it at all.

    This is the error box:

    ---------------------------
    Microsoft Visual Basic
    ---------------------------
    Compile error:

    Variable required - can't assign to this expression
    ---------------------------
    OK Help
    ---------------------------



    But when I declared it as "Object", it was happy.

    That's the only puzzling thing for me, since clearly it's a "Shape".



    I am going to mark this as SOLVED since I have two different solutions here that approach this issue from two different angles.
    EDIT: Hmm, can't see a way to mark it as "SOLVED". Should I worry about this?


    *If someone uses Alternative Text method, make sure that you really don't have anything in the Alternative Text box (Format Autoshape), or this is not going to work.



    Thanks,
    a1
    Last edited by a1s2d3f4; 08-06-2009 at 09:13 AM.

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,945

    Re: Find out if Shape has Text

    The error when declaring as shape is due to the incorrect usage of Len/Count.
    Once the compiler knows what the object is it can object

    Please Login or Register  to view this content.
    I will mark the post as solved. There is a time limit for users.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1