VBA - Moving and resizing chart

Posted on 2012-09-05
Last Modified: 2012-09-06
Hi Guys,

I have the code below which will create a chart based on a template. This works fine however the chart when enbedded on the worksheet is too small and in the wrong position.
Is there a way using VBA code to resize the chart and also move it to the correct position on the sheet. Also, the title and vertical axis comes out empty. I can set the title as seen below but can't work out how to set the vertical axis title

ActiveChart.setSourceData Source:=Range("'Quality'!$A$19:$D$28")
ActiveChart.ChartTitle.Text = "Quality"

Thanks heaps

Question by:victoriaharry
    1 Comment
    LVL 18

    Accepted Solution


    try something like

    Dim TopLeftCell     As Range
    Dim chtChtObj       As ChartObject
    Dim Sht             As Worksheet
    Set Sht = ActiveSheet
    Set TopLeftCell = Sht.Range("a1")
    Set chtChtObj = Sht.ChartObjects.Add(TopLeftCell.Left + 2, TopLeftCell.Top + 2, 400, 200)
    chtChtObj.Chart.SetSourceData Range("'Quality'!$A$19:$D$28")
    With chtChtObj.Chart
        .ApplyChartTemplate ("Quality.crtx")
        If Not .HasTitle Then .HasTitle = True
        .ChartTitle.Text = "Quality"
        If Not .Axes(2, xlPrimary).HasTitle Then .Axes(2, xlPrimary).HasTitle = True
        .Axes(2, xlPrimary).AxisTitle.Text = "My Title"
    End With

    Open in new window


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now