Formatting with graphs
I have a sheet and I am creating a chart from a table.
I would like to create 2 diagrams. One chart with absolute numbers and another chart for the same data with percentage.
Right now, I am using two codes for this, just adding a line to generate a chart with Y.axis as a percentage.
I would like to define the column where my chart will start (for example chart1 from G7) and chart2 from G15. (I don't have this in my code)
I would also like to define the length, height and width for my graph. (I don't have this in my code)
It would be great if you could help me add this requirement and do it in one 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 am using the same code deleting the line to create the second chart
cht.Axes(xlSecondary).TickLabels.NumberFormat = "0.0%"
source to share
The easier it is to use ChartObject
to create and define a chart, and then change all its properties (such as position and dimension).
The code below will create the first graph, place it in cell "G7" and I have resized it to show you the properties that need to be changed.
You can add another one for the second graph (with 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
source to share
To change the location of the chart:
vba add shape to specific cell in Excel
For size chart:
sh.Width = 100 sh.Height = 100
source to share