+ Reply to Thread
Results 1 to 3 of 3

Loop thru Charts in Workbook and Changing Chart 2/Error Handling

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Loop thru Charts in Workbook and Changing Chart 2/Error Handling

    Hi, I have code that will change data labels, now I want to loop through all the worksheets and change chart 2. Not every sheet has a chart, but if there are charts there is a chart 1 2 and 3.
    My current code activates chart 2 and then changes it, but that doesn't work with looping and I'm stuck on how to reference it.
    Portion of code below

    
    Dim WSC As Integer
    Dim I As Integer
             
    WSC = ActiveWorkbook.Worksheets.Count
    
        For I = 1 To WSC
    
    
    Dim s1p1, s1p2, s1p3, s1p4 As Long
    Dim s2p1, s2p2, s2p3, s2p4 As Long
    
    ActiveSheet.ChartObjects("Chart 2").Activate
    
        s1p1 = CLng(ActiveChart.SeriesCollection(6).Points(1).DataLabel.Text)
        s1p2 = CLng(ActiveChart.SeriesCollection(6).Points(2).DataLabel.Text)
        s1p3 = CLng(ActiveChart.SeriesCollection(6).Points(3).DataLabel.Text)
        s1p4 = CLng(ActiveChart.SeriesCollection(6).Points(4).DataLabel.Text)
        
        s2p1 = CLng(ActiveChart.SeriesCollection(5).Points(1).DataLabel.Text)
        s2p2 = CLng(ActiveChart.SeriesCollection(5).Points(2).DataLabel.Text)
        s2p3 = CLng(ActiveChart.SeriesCollection(5).Points(3).DataLabel.Text)
        s2p4 = CLng(ActiveChart.SeriesCollection(5).Points(4).DataLabel.Text)
    
    'point 1
        If s1p1 > s2p1 Then
           ActiveChart.SeriesCollection(6).Points(1).DataLabel.Position = xlLabelPositionAbove
            ActiveChart.SeriesCollection(5).Points(1).DataLabel.Position = xlLabelPositionBelow
        Else: ActiveChart.SeriesCollection(6).Points(1).DataLabel.Position = xlLabelPositionBelow
            ActiveChart.SeriesCollection(5).Points(1).DataLabel.Position = xlLabelPositionAbove
        End If
    'more code...
    
            Next I
    'end
    Thank you.
    Last edited by ker9; 03-29-2014 at 09:32 AM.

  2. #2
    Forum Contributor
    Join Date
    06-16-2010
    Location
    Tampa, FL
    MS-Off Ver
    Excel 2010
    Posts
    278

    Re: Loop thru Charts in Workbook and Changing Chart 2/Error Handling

    I've gotten further along as I continue to try to make this work.

    Now I need to handle errors, but I'm not sure what types of errors I might come across.
    It's possible a chart might be missing a data label to work with. What is best error handling method?

    Looping code:
    Sub MoveDataLabels2()
    
    Dim ws As Worksheet
    Dim objCht  As ChartObject
    
    
    For Each ws In ActiveWorkbook.Worksheets
    
        For Each objCht In ActiveSheet.ChartObjects
            With objCht.Chart
                    objCht.Activate
                       Call MoveDataLabels
            End With
        Next
        
        ActiveSheet.Range("AJ15").Select 'this is a place to break, don't really need it
        
    Next ws
    
    End Sub

  3. #3
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Loop thru Charts in Workbook and Changing Chart 2/Error Handling

    Hi ker9,

    Good progress.

    Some of the problems include:
    a. Data Series missing
    b. Data point missing
    c. Data label missing (if point is missing)
    d. Data label text is not a number (improbable but possible)

    Start deleting things one at a time until you get a runtime error. Then when you find which runtime errors you get you can process or ignore the possible errors.

    For example you could use something like this as a starting point.
    Sub MainRoutine()
        '...
        s1p1 = GetDataLabelAsLongInteger(6, 1)
        s1p2 = GetDataLabelAsLongInteger(6, 2) 
        '...
    End Sub
    
    Function GetDataLabelAsLongInteger(iSeriesNumber As Integer, iPointNumber As Integer) As Long
    
      Dim iError As Long
    
      On Error Resume Next
      GetDataLabelAsLongInteger = CLng(ActiveChart.SeriesCollection(iSeriesNumber).Points(iPointNumber).DataLabel.Text)
      
      iError = Err.Number
      Select Case iError
        
        Case 0
          'do nothing - no error
            
        Case 1004
          'Method 'SeriesCollection' of object '_Chart' Failed'
          'Stop, ignore, or perform automatic corrective action as required
          Debug.Print "Series " & iSeriesNumber & "  Point " & iPointNumber & "  Runtime Error " & iError
            
        Case Else
          'Stop, ignore, or perform automatic corrective action as required
          Debug.Print "Series " & iSeriesNumber & "  Point " & iPointNumber & "  Runtime Error " & iError
      End Select
        
      On Error GoTo 0
    
    End Function
    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Loop for a folder of all the files, Loop all the worksheet in each workbook
    By nanjingwoodworking in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2013, 07:20 PM
  2. Replies: 0
    Last Post: 07-20-2010, 11:42 AM
  3. Titles on Charts in a loop
    By jcfryman in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-02-2009, 08:38 AM
  4. [SOLVED] Problem adding charts using Do-Loop Until loop
    By Chris Bromley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2005, 09:06 AM
  5. help with for loop for charts
    By steve_bris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2005, 07:34 AM

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