Updating Chart with VBA [SOLVED]

  • Hi All,

    I'm currently using the following code to update an Excel Charts X Axis scale.

    Application.Sheets("Gantt Chart").Select
    Application.ScreenUpdating = False
    Application.Sheets("Gantt Chart").Activate
    With ActiveChart.Axes(xlValue)

    .MinimumScale = Application.Sheets("Schedule").Range("D8") - "5"
    .MaximumScale = Application.Sheets("Schedule").Range("F24") + "5"
    .MinorUnit = 1
    .MajorUnit = 7
    .Crosses = xlCustom
    .CrossesAt = Application.Sheets("Schedule").Range("D8") - "5"
    .ReversePlotOrder = False
    .ScaleType = xlLinear
    End With

    This works great with the chart as a sheet within the workbook. The problem now is that due to additional data requirements I've had to place the chart in the Schedule Sheet and then had to superimpose 2 additional charts over top with the same scaling that show updated information.

    I can get the chart numbers ie. ActiveSheet.ChartObjects("Chart 1082").Activate. But because I'm using a template each time a new workbook is created the chart numbers change.

    Ok to cut to the chase - I need a way to return the numbers of the ChartObjects in the Activesheet so that I can loop through them and apply the scaling shown above to the X Axis of each.

    I know it's possible but just can get my head around it.

    Dim ch As ChartObject
    Set ch = Worksheets("Schedule").ChartObjects.????????

    Then something with - for each ch in worksheet ??



  • Hi AJW,

    How about this, it just displays the chartname of all chartobjects on activesheet.

    Sub x()
    Dim objCht As ChartObject
    For Each objCht In ActiveSheet.ChartObjects
    MsgBox "Chartobject " & objCht.Name
    End Sub

    You say you are super imposing charts. Is it not possible to combine the charts into one single chart?
    If you post an example I would be willing to take a look see.


  • Thanks Andy,

    Appreciate it immensly when a charting guru answers........way cool.

    Attached example, it's a work in progress so please feel free to ignor the mess.

    I have removed a few things that reference my workplace.



  • Hi Tony,

    I have managed to condense the three charts in to a single one.

    The easy bit is adding the other two sets of data to the main chart.
    The hard part is creating some formula to re adjust the values.

    These formula are behind the chart.

    All should be ok, unless the actual and or forecast days can extend passed the original allocated days.
    This is not so much a problem simply that I do not know your rules.
    So you may have to adjust the formula to take any oddities into account.

    The formulas should be easy to understand.
    What they are trying to do is to produce 3 values that reflect the construction of the number of allocated days.
    Any problems with understanding the formula let me know.


  • Andy,

    THANKYOU ! Thankyou so very much for your help, it is greatly appreciated.

    I messed around for half the day yesterday trying to do what you have achieved. (you make it look so easy).

    I love working data through charts and graphs, I guess it's gonna take a little longer to get to your level of knowledge with them.

    On the topic of charts and graphs I have developed a little equation finder that extracts the equation for line of best fit on plotted data. (I believe there is a function in Excel that does this also but have been unable to "discover" it yet.)

    I attach it for interest of yourself and others more than anything else. perhaps you have a better (easier) way of achieving the same outcome ?

    I would be interested in your comments if you have time to review.......mmmm....kinda feels like I'm submitting my homework ;)

    Best Regards

    Tony (AJW)

  • Quote

    Originally posted by Ivan F Moala
    Have a look @ Evaluate Method

    Hi Ivan,

    Excuse my ignorance, evalute ???

    Checked it out, it applies to Application, Chart and Worksheet object.

    I'm assuming I apply it to a range within the worksheet that contains my data ?

    And that I use some definer to tell it how to evalute the range ?


  • sought of, I've used it to get the Formulas you get when doing Charts, as you are aware you can put formulas on charts (Trends) this is what I'm refereing to....

  • Ivan,

    OK now I'm with you. So you get the formula from the graph i.e

    Dim strLabelA As String
    strLabelA = Application.ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text

    And then use Evaluate Method to evaluate the formula within the VBE ??

    Currently I'm puling the formula out and then carrying out a series of Substitue functions to make it acceptable to Excel and then putting it into the worksheet. i.e

    strLabelA = Application.ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
    strLabelB = Application.WorksheetFunction.Substitute(strLabelA, "x6", "*C6^6")
    strLabelC = Application.WorksheetFunction.Substitute(strLabelB, "x5", "*C6^5")
    strLabelD = Application.WorksheetFunction.Substitute(strLabelC, "x4", "*C6^4")
    strLabelE = Application.WorksheetFunction.Substitute(strLabelD, "x3", "*C6^3")
    strLabelF = Application.WorksheetFunction.Substitute(strLabelE, "x2", "*C6^2")
    strLabelG = Application.WorksheetFunction.Substitute(strLabelF, "x", "*C6")
    strLabelH = Application.WorksheetFunction.Substitute(strLabelG, "y ", "")
    Application.Range("Y_Axis_Calced").Formula = strLabelH

    Obvoiusly C6 refers to the X axis value to use in the formula.

    I'll need to look into the Evaluate Method a bit further to see if it can do the same but cleaner.



  • Hi Andy,

    Thanks for the link. The functions look quite involved comparred to the method I'm currently using.

    When time permits I'll have a closer look and compare results.

    Thanks again


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!