Solved: VBA Graph - Tweaks Needed to Amend Font and Placement?!! HELP!

[SDave]

Quick question - hopefully. I've created a VBA code which maps a graph based on a selected range - see below:

Sub BartonChart()
Application.ScreenUpdating = False
Charts.Add
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Summary").Range("D39:P40,D48:P49,D57:K58")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Barton"
ActiveChart.SeriesCollection(1).Name = "Barton Summary"
ActiveChart.HasLegend = False
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 15
.Weight = xlThin
.LineStyle = xlNone
End With
Selection.Interior.ColorIndex = xlNone
Application.ScreenUpdating = True
MsgBox ("Graphic Mapped")
End Sub

Is it at possible to specify the Font to be Arial, size 8 and place the graph over C9:N27?!

Any help would be much appreciated.

Thanks.

[rbrhodes]

Hi sDave,

Not that quick...


Option Explicit
Sub BartonChart()
Dim sName
Dim cName As String
Application.ScreenUpdating = False

Charts.Add

With ActiveChart
'.ChartArea.Select
.ChartType = xlLine
.SetSourceData Source:=Sheets("Summary").Range("D39:P40,D48:P49,D57:K58")
.SeriesCollection(1).Name = "Barton Summary"
.HasLegend = False
.Location Where:=xlLocationAsObject, Name:="Barton"
End With

'Split name on space
sName = Split(ActiveChart.Name, " ")
'Drop sheet name
cName = sName(1) & " " & sName(2)

With ActiveSheet.Shapes(cName)
.Left = Sheets("Summary").Range("C9").Left
.Top = Sheets("Summary").Range("C9").Top
.Height = Sheets("Summary").Range("C9:N27").Height
.Width = Sheet1.Range("C9:N27").Width
End With

With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
.TickLabels.AutoScaleFont = False
End With

With ActiveChart.Axes(xlValue).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With
ActiveChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
With ActiveChart.Axes(xlCategory).TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
End With
With ActiveChart.PlotArea.Border
.ColorIndex = 15
.Weight = xlThin
.LineStyle = xlNone
End With
ActiveChart.PlotArea.Interior.ColorIndex = xlNone

Application.ScreenUpdating = True

MsgBox ("Graphic Mapped")
End Sub

[SDave]

Thanks dr, you're a star - much appreciated.

I've got one more question - sorry to be a pain.

I'm trying to insert values for my X axis but I keep receiving a compile error.

The script I'm trying to insert is as follows:

.SeriesCollection(1).XValues = ("Summary").Range("D39:P39,D48:P48,D57:K57")

Which I've inserted directly after (second line down after With ActiveChart) :

.SetSourceData Source:=Sheets("Summary").Range("D40:P40,D49:P49,D58:K58")

Again sorry to be a pain!

[rbrhodes]

Hi d

Well I got it to run the code but...

There seem to be two diffferent formats:

.SeriesCollection(1).XValues = Sheets("Summary").Range("D39:P39")
or
.SeriesCollection(1).XValues = "=Summary!R39C4:R39C16"

but that's not the problem. Doesn't seem to like non-contig ranges.

I did notice that each of your ranges counts as a Seriescollection item.

HTH




With ActiveChart
'.chartArea.Select
.ChartType = xlLine
.SetSourceData Source:=Sheets("Summary").Range("D39:P40,D48:P49,D57:K58")
.SeriesCollection(1).Name = "Barton Summary"

.SeriesCollection(1).XValues = Sheets("Summary").Range("D39:P39") 'rest of ranges left out...

'or
'.SeriesCollection(1).XValues = "=Summary!R39C4:R39C16" ',r48c4:r48c16,r57c4:r57c11"


.HasLegend = False
.Location Where:=xlLocationAsObject, Name:="Barton"
End With

[SDave]

Thanks dr - your a gem. Much appreciated!!!