[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

VBA - Moving and resizing chart

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

Range("A19:D28").Select
ActiveSheet.Shapes.AddChart.select
ActiveChart.setSourceData Source:=Range("'Quality'!$A$19:$D$28")
ActiveChart.ApplyChartTemplate("Quality.crtx")
ActiveChart.ChartTitle.Text = "Quality"

Thanks heaps

Gav
0
victoriaharry
Asked:
victoriaharry
1 Solution
 
krishnakrkcCommented:
Hi

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


Kris
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now