Results 1 to 9 of 9
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Determine Type Of Selection (All)

    I am writing a small utility to allow users to update several selected charts (chartobjects).
    For this I loop through the currently selected set of ChartObjects and make changes (Easy)
    BUT.. How do I determine if the current selection is a set of Chart Objects
    Also, How do I determine how many there are.

    I was hoping to use

    For Each objChart in Selection

    This works OK If more than one.

    I can get round the NOT a chart Object using an error trap, but cannot determine how many chart objects are in the selection.

    SO... Is there any way to determine what type of object the current selection is
    AND..Is there any way to Count the number of Items in the selection

    Selection.count comes up with an error if charts are selected
    For each objChart comes up with an error if ONLY one chart is selected


    I am sure there is an easy solution, but google just goes off into a world of unrelated trivia if I search it.

    Thanks
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine Type Of Selection (All)

    You can inspect TypeName(Selection).

    If the current selection is a range of cells, this returns "Range".
    If the current selection is a single chart, this returns "ChartArea".
    If the current selection is a single drawing object such as an oval or rectangle, it returns "Oval" or "Rectangle" etc.
    If the current selection consists of several charts and/or shapes, it returns "DrawingObjects".

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine Type Of Selection (All)

    Thanks Hans.

    I discovered another bug anyway.

    If I use for each objChart in Selection

    then if more than chart is selected it processes ALL the charts on the sheet
    rather than just the selected one, so I will need to use this and loop through the drawingobjects collection

    However I think now if I use Typename and a select case statemeny=t then all will be ok.

    Shame Google or the help did not show up Typename

    I should have asked you first.

    Andrew
    Andrew

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine Type Of Selection (All)

    Nearly cracked now.
    BUT...

    If I do ......... For Each objChart In Selection
    It processes ALL the ChartObjects NOT just those that are selected.

    How do I find out which objects are actually part of the selection?

    Is this somethin g to do with the Shaperange Object?
    Andrew

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine Type Of Selection (All)

    I don't understand that. If I select 2 charts on a worksheet with 3 charts, the code

    Dim cht As ChartObject
    For Each cht In Selection
    Debug.Print cht.Name
    Next cht

    returns 2 names, not 3...

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine Type Of Selection (All)

    Excel 2007!!!!

    It works fine in Excel 2000-2003

    But 2007 Now returns "ChartObjects" against the MultiSelection with Typename.
    (All the others are the same as in 2000-2003)

    When you loop through the selection it processess all of them
    But in 2000-2003 it just does the selected ones.

    Not sure now if there is a solution or if it is yet another bug.

    I am working with 2007 Excel with the most up to date SP 3.

    I'll keep playing with it but if anyone can find a solution that would be great
    Andrew

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine Type Of Selection (All)

    There was no indication anywhere in this thread that the problem occurred in Excel 2007. I don't have Excel 2007 so I can't help you with this, sorry.

    (The most recent version of Office 2007 is SP1, by the way, SP3 is still in the future...)

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine Type Of Selection (All)

    Valid point on Sp3.
    Office 2003 is SP3 Office 2007 is SP1

    There is NO 2007 in the thread because I was after a solution that would work in all versions.
    For 2000 to 2003 the function and the return items was spot on and worked well so thanks.

    It was not until I tried it in 2007 that I found there was an issue.
    The actual file is saved in compatible .xls mode from 2007.
    It would appear that the problem lies in changes to the behavior of the properties of the object model between 2003 and 2007.
    I'll see if there is a work around.
    I can always add some code to get the routine to determine which version of office it is running under and take the appropriate course of action.
    I just need to figure out now how to make it work in 2007.
    So if anyone has any ideas.
    Andrew

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Determine Type Of Selection (All)

    There is definitely some sort of issue with selection and ChartObjects in in Excel 2007

    I tried Selection.Count and Selection.ShapeRange.Count with different combinations of Objects

    I have a sheet with 2 Picture, 3 Rectangles and 3 ChartObjects

    Selection.Count Gives Selection.ShapeRange.Count
    If I select
    2 Chart Objects (2) Selection.Count= 3 Selection.ShapeRange.Count = 3 ****
    2 pictures (2) Selection.Count= 2 Selection.ShapeRange.Count = 2
    2 Rectangles (2) Selection.Count= 2 Selection.ShapeRange.Count = 2
    1 Chart Object and 1 Picture (2) Selection.Count= 3 Selection.ShapeRange.Count = 2
    2 Rectangles and 1 Picture (2) Selection.Count= 4 Selection.ShapeRange.Count = 3
    2 Rectangles and 2 Pictures (4) Selection.Count= 5 Selection.ShapeRange.Count = 4
    1 Picture and 1 ChartObject (2) Selection.Count= 3 Selection.ShapeRange.Count = 2
    1 Picture and 2 ChartObjects (3) Selection.Count= 4 Selection.ShapeRange.Count = 3
    1 Picture, 1 Chart Object and 1 Rectangle (3) Selection.Count= 4 Selection.ShapeRange.Count = 3

    It would appear that ShapeRange returns the correct number of selected items
    in most cases .

    It gets even odder if I have 4 Chart Objects

    If I then select (2) and ask Selection.Count = 4 Selection.ShapeRange.Count=2

    But if I select (3) ChartObjects then Selection.Count=4 and Selection.ShapeRange.Count = 2

    So am I being stupid?
    Using the wrong method
    Or is there an issue with ChartObjects in Excel 2007

    Cos it all works fine in earlier versions
    Andrew

Posting Permissions

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