Formatting with Charts

Question

I have a sheet and I am generating the Chart from a table.

I would like to generate 2 charts. One chart with absolute numbers and other chart for the same data with Percentage.

Right now, for this, I am using two code, just by adding a line for generating the chart with Y.axis in percentage.

I would like to define column where my chart will start (for eg: chart1 from G7) and chart2 from G15. (I don't have this in my code)

I also, would like to define the length , height and width for my chart.(I don't have this in my code)

It would be great if you can help me add this requirement and do it In a single program.

Sub chartstatus()

Dim rng As Range
Dim cht As Object

Set rng = ActiveSheet.Range("A2:E53")

Set sh = ActiveSheet.Shapes.AddChart
sh.Select
Set cht = ActiveChart
With cht
    .SetSourceData Source:=rng
    .ChartType = xlColumnClustered
    cht.Axes(xlSecondary).TickLabels.NumberFormat = "0.0%"
End With

cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 255, 255) '<~~ Red
cht.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
cht.SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
cht.HasTitle = True
cht.ChartTitle.Text = "Result 2017"

End Sub

I use the same code, deleting the line to generate the second chart

cht.Axes(xlSecondary).TickLabels.NumberFormat = "0.0%"

Answer 1

For changing the chart location :

vba to add a shape at a specific cell location in Excel

For chart size:

sh.Width =100 sh.Height =100

Answer 2

The easier way if to use the ChartObject to create and define the chart, and then modify all it's properties (such as position and dimension).

The code below will create the first chart, place it in Cell "G7", and I modifed it's dimensions to show you the properties you need to modify.

You can add another one for the second chart (with an easy copy>>paste).

Code

Option Explicit

Sub chartstatus()

Dim Rng As Range
Dim ChtObj As ChartObject

Set rng = ActiveSheet.Range("A2:E53")

' use ChartObject instead of shape
Set ChtObj = ActiveSheet.ChartObjects.Add(100, 100, 500, 500) '<-- default dimension and location >> can modify later
With ChtObj
    .Chart.ChartType = xlColumnClustered
    .Chart.SetSourceData Rng

    With .Chart
        .Axes(xlSecondary).TickLabels.NumberFormat = "0.0%"

        .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 255, 255) '<~~ Red
        .SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        .SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
        .HasTitle = True
        .ChartTitle.Text = "Result 2017"
    End With

    ' set position of the chart to Cell G7
    .Top = Range("G7").Top
    .Left = Range("G7").Left

    ' change the dimensions of the chart
    With .Chart.ChartArea
        .Width = 1060
        .Height = 420
    End With
End With

End Sub