Results 1 to 14 of 14
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Loop through Selected Charts on a Worksheet Excel 2007 and above

    My GoogleFu is weak today. I've been trying to figure out how to loop through a collection of Charts on a Worksheet.Lots of example of how to loop through EVERY chart, but I only want to loop through the ones the user has selected.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    No it doesn't, the selection count is applied to the order of charts on the sheet - not the selected charts.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Catharine,

    This is air code but how about?

    Code:
        Sub .....
    
            Dim chtCurChart as Chart
    
            With Selection
                  For Each chtCurChart in Selection.Charts
                      ----Processing Code here  ------
                  Next chtCurChart
            End With   'selection
    
        End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I know, it looks like Selection.Charts should work. However, it seems that when multiple charts are selected they actually are selected as DrawingObjects I'm finding it a challenge to unpack how DrawingObjects are referenced and turn it back into charts.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Catharine,

    I'm stumped too after trying some ideas out. The only solution I can come up with is to loop through all the charts.
    Code:
    Sub ChartLoop()
        
        Dim objCht As ChartObject
         
        For Each objCht In ActiveSheet.ChartObjects
           With objCht
    
            ' Debug.Print .Chart.Name, .Chart.SeriesCollection(1).Interior.Color
             
           End With
        Next
         
    End Sub
    Then display the chart name, ask the user if they wish to process this chart. If yes store in an array then after the list is generated use it to loop through the selected charts and do your processing. Not pretty but should work, but maybe not in your situation.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Code:
    Sub SelectedCharts()    Dim shp As Shape
        If TypeName(Selection) = "DrawingObjects" Then
            For Each shp In Selection.ShapeRange
              If shp.Type = msoChart Then
                MsgBox shp.Chart.Name
            End If
            Next shp
        End If
    End Sub
    should work though I only tested in 2010.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Interesting Rory, I tested in 2010 & 2007. Like you I found it works in 2010. It does not work in 2007. I'm still racking my brain, trying to think of a 2007 method.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I don't think I have 2007 installed anywhere (since I loathe it) but will set up a VM and do some testing.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Working 2010 Version

    Here is a working 2010 version, I've been playing with various chart elements to edit.
    Interestingly, I had to clip the sheet name off of the chart name to work with it.

    Sub SelectedCharts()
    'works in 2010 only
    Dim shp As Shape
    Dim strName As String
    Dim strSheetName As String
    If TypeName(Selection) = "DrawingObjects" Then
    For Each shp In Selection.ShapeRange 'breaks here in 2007
    If shp.Type = msoChart Then
    strName = shp.Chart.Name
    strSheetName = ActiveSheet.Name
    strName = Right(strName, Len(strName) - (Len(strSheetName) + 1))
    ActiveSheet.ChartObjects(strName).Activate
    With ActiveChart
    .SeriesCollection(1).ChartType = xlBarClustered
    .Axes(xlValue).MaximumScale = 1
    .SetElement (msoElementPrimaryValueAxisNone) 'removes axis
    .ChartArea.Format.Line.Visible = msoFalse
    .ChartArea.Format.Fill.Visible = msoFalse
    .Parent.Height = 11.34
    .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(230, 75, 150)
    End With
    End If
    Next shp
    End If
    End Sub
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Aha - in 2007 the shape you get is actually an Office.Shape and not an Excel.Shape and so its Chart property returns an IMsoChart object rather than an Excel.Chart. What you can use is something like:
    Code:
    Sub SelectedCharts()
       Dim cht                    As Excel.Chart
       Dim shp                    As Object
       If TypeName(Selection) = "DrawingObjects" Then
          For Each shp In Selection.ShapeRange
             If shp.Type = msoChart Then
                Set cht = ActiveSheet.ChartObjects(shp.Name).Chart
                MsgBox shp.Chart.Name
             End If
          Next shp
       End If
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    HOW did you track it down Rory??
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Tried to assign the Chart property to a Chart variable and got a type mismatch. Examining the shape in the locals window gave the game away.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Loop through Selected Charts on a Worksheet 2007&2010

    After a small modification, I got this to work in both 2007 & 2010. Interestingly, once the shape is defined as an object - editing the object name becomes unneccessary.


    <code>
    Sub SelectedCharts()
    'Works in both 2007 and 2010
    Dim cht As Excel.Chart
    Dim shp As Object

    Dim strName As String
    Dim strSheetName As String
    If TypeName(Selection) = "DrawingObjects" Then
    For Each shp In Selection.ShapeRange
    If shp.Type = msoChart Then
    strName = shp.Name
    strSheetName = ActiveSheet.Name
    ActiveSheet.ChartObjects(strName).Activate
    With ActiveChart
    .SeriesCollection(1).ChartType = xlBarClustered
    .Axes(xlValue).MaximumScale = 1
    .SetElement (msoElementPrimaryValueAxisNone) 'removes axis
    .ChartArea.Format.Line.Visible = msoFalse
    .ChartArea.Format.Fill.Visible = msoFalse
    .PlotArea.Format.Fill.Visible = msoFalse
    .Parent.Height = 11.34
    .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(50, 125, 50)
    End With
    End If
    Next shp
    End If
    </code>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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