+ Reply to Thread
Results 1 to 11 of 11

determining selection is array of shapes

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

    determining selection is array of shapes

    I have a function that I call with the name of a shape that exists on my worksheet.

    I actually select that shape on the worksheet, then execute a macro and since it is a single shape selection, it is easy to get its name:

    Please Login or Register  to view this content.

    However, I also want to be able to select multiple shapes (via ctrl-click) on my worksheet, and still to be able to successfully use my macro.

    Obviously, in the case of multiple shapes the Selection contains e.g.:

    ActiveSheet.Shapes.Range(Array("Shape 1", "Shape 2", etc. etc))


    What code do I use to find out:
    a) Whether the Selection is that of multiple shapes (array of shapes), or just a single shape
    b) If it is an array of shapes, how many there are
    c) Also, how do I iterate through those names (so that I can pass each one to my function inside some kind of loop)?


    Thanks,
    a1

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

    Re: determining selection is array of shapes

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

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

    Re: determining selection is array of shapes

    This uses the ShapeRange collection and seems to work:
    Please Login or Register  to view this content.

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

    Re: determining selection is array of shapes

    StephenR saves the day yet again.

    Thanks.

    p.s. Is there a way to check if I have nothing selected?

  5. #5
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: determining selection is array of shapes

    Try this. romperstomper did get there before me so please at least acknowledge his post.
    Please Login or Register  to view this content.

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

    Re: determining selection is array of shapes

    Thanks again.
    And thanks to romperstomper. I simply tried the last solution first and it worked so, I immediately replied about that.

    The sad part for me is that I realized now that the function that I call uses .Select method on the shape I pass in, and so, I end up losing the multiple shape selection and the code no longer works.

    I need to figure out now how to Dim an array of strings, read in the names of all the selected shapes, and then reference those names in this string array when I do the For Next loop.

    Thanks,

    a1

  7. #7
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: determining selection is array of shapes

    Don't quite follow your problem but here is how you could put the names into an array (v).
    Please Login or Register  to view this content.

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

    Re: determining selection is array of shapes

    Ok, I figured out how to declare a string array - Dim asdf(number) As String - although it is not dynamic (have to assume I won't be selecting more than 50 shapes at a time.

    Now will try to wrap up by "restoring" the initial group selection by somehow adding all the items in my string array to a shape selection...but that's probably for a different post.

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

    Re: determining selection is array of shapes

    Ha, seems like we posted stuff together.
    I will look at your solution (I don't know what ReDim is yet).

    If you are referring to the problem that I mentioned in regards to losing the selection, I was simply trying to explain that the function which I call ends up selecting "from code" the shape the name of which I give to that function. Thus, the group selection through which your code successfully iterated is lost. (i.e. I changed your code thus):

    Please Login or Register  to view this content.

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

    Re: determining selection is array of shapes

    Is there a reason for your function selecting the shape?

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

    Re: determining selection is array of shapes

    Yes, there is. (At least, currently there is).

    Here is the part of the function I am calling that sets Selection to the single shape.

    Please Login or Register  to view this content.
    As you can see, it's a bit of a mess. I am basically trying to "dim" (change the color to light gray) text which happens in some of my shapes.

    Because not all shapes have text in them, I am having to do all kinds of Ifs to avoid breaking code.

    This function, in theory, has a way to access the name of the shape inside which I want to dim the text (if there is some) so there is probably a way to code it more efficiently using that. I am sure there is. But I don't have the time at this point to work on optimization (need a little more VBA experience before I get there).

    This probably for a different thread, though. Thanks for asking.

+ 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