Excel VBA: Control Font Size on Charts

[xlStar]

I am struggling to control the font size! Below, the Chart Title ended up with size 19.75 and legend font as size 11.75. Why?


Sub MakeDoubleBars(endrow As Integer)
Dim DCht As Chart

Set DCht = Charts.Add
Set DCht = DCht.Location(Where:=xlLocationAsObject, Name:="D Chart")
With DCht
.ChartType = xl3DColumnClustered
.SetSourceData Source:=Sheets("D Chart").Range("B1:B" & endrow & ",C1:C" & endrow & ",E1:E" & endrow), PlotBy:=xlColumns
With .Parent
.Top = Range("F1").Top
.Left = Range("F1").Left
.Width = Range("F125").Width
.Height = Range("F125").Height
End With

.Rotation = 20
.RightAngleAxes = True
.AutoScaling = True

.HasLegend = True
.Legend.Position = xlLegendPositionBottom
.Legend.Font.Size = 6
.HasDataTable = False

.HasTitle = True
.ChartTitle.Characters.Text = "Annual Revenue Budget"
.ChartTitle.Font.Size = 10
.ChartTitle.Font.Underline = xlUnderlineStyleSingle

.Axes(xlValue).TickLabels.AutoScaleFont = False
.Axes(xlValue).TickLabels.Font.Size = 6
.Axes(xlValue).TickLabels.NumberFormat = "$#,##0_);[Red]($#,##0)"

.Axes(xlCategory).TickLabelPosition = xlLow
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlCategory).TickLabels.AutoScaleFont = False
.Axes(xlCategory).TickLabels.Font.Size = 6

.SeriesCollection(1).Interior.ColorIndex = 36 'Yellow
.SeriesCollection(2).Interior.ColorIndex = 10 'Green

End With
End Sub

 

[xlStar]

nor this one!


Sub MakeDoubleBarsSJ(inc As Integer, exp As Integer) '(endrow As Integer)

Dim DCht1 As Chart
Set DCht1 = Charts.Add
Set DCht1 = DCht1.Location(Where:=xlLocationAsObject, Name:="SJ Chart")
With DCht1
.ChartType = xl3DColumnClustered
.SetSourceData Source:=Sheets("SJ Chart").Range("B1:B" & inc & ",C1:C" & inc & ",E1:E" & inc), PlotBy:=xlColumns
With .Parent
.Top = Range("F1").Top
.Left = Range("F1").Left
.Width = Range("F125").Width
.Height = Range("F125").Height
End With

.Rotation = 20
.RightAngleAxes = True
.AutoScaling = True

.HasLegend = True
.Legend.Position = xlLegendPositionBottom
.Legend.Font.Size = 8
.HasDataTable = False

.HasTitle = True
.ChartTitle.Characters.Text = "Annual Revenue Budget"
.ChartTitle.Font.Size = 10
.ChartTitle.Font.Underline = xlUnderlineStyleSingle

.Axes(xlValue).TickLabels.Font.Size = 6
.Axes(xlValue).TickLabels.NumberFormat = "$#,##0_);[Red]($#,##0)"

.Axes(xlCategory).TickLabelPosition = xlLow
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlCategory).TickLabels.Font.Size = 6

.SeriesCollection(1).Interior.ColorIndex = 36 'Yellow
.SeriesCollection(2).Interior.ColorIndex = 10 'Green
End With

'Expenditure
Dim DCht2 As Chart
Set DCht2 = Charts.Add
Set DCht2 = DCht2.Location(Where:=xlLocationAsObject, Name:="SJ Chart")
With DCht2
.ChartType = xl3DColumnClustered
.SetSourceData Source:=Sheets("SJ Chart").Range("B9:B" & exp & ",C9:C" & exp & ",E9:E" & exp), PlotBy:=xlColumns
With .Parent
.Top = Range("F27").Top
.Left = Range("F27").Left
.Width = Range("F2752").Width
.Height = Range("F2752").Height
End With

.Rotation = 20
.RightAngleAxes = True
.AutoScaling = True

.HasLegend = True
.Legend.Position = xlLegendPositionBottom
.Legend.Font.Size = 8
.HasDataTable = False

.HasTitle = True
.ChartTitle.Characters.Text = "Annual Revenue Budget"
.ChartTitle.Font.Size = 10
.ChartTitle.Font.Underline = xlUnderlineStyleSingle

.Axes(xlValue).TickLabels.Font.Size = 6
.Axes(xlValue).TickLabels.NumberFormat = "$#,##0_);[Red]($#,##0)"

.Axes(xlCategory).TickLabelPosition = xlLow
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlCategory).TickLabels.Font.Size = 6

.SeriesCollection(1).Interior.ColorIndex = 36 'Yellow
.SeriesCollection(2).Interior.ColorIndex = 10 'Green
End With

End Sub

 

[asrisk]

You could try setting the .Autoscalefont property to false for each object where you set the font size, for example

.Axes(xlCategory).TickLabels.AutoScalefont = False
.Axes(xlCategory).TickLabels.Font.Size = 6

.

Otherwise, font sizes are scaled depending upon the size of a chart. Bonus point to anyone who can offer a way to turn this "feature" off by default...

-------
I am not responsible for any "Sponsored Links" which may appear in my messages.

 

[xlStar]

On my second VBA script. Just before the DCht2 is being use and after DCht1 have finished. I enter Range("F1").Select.

This works fine (the Font agreed to exactly what VBA script is), taking the Range("F1").Select off, the font on the second chart gone haywire. I am assuming the first chart is still activated.


So, instead of Range("F1").Select, how can I de-select Chart 1 before adding chart 2. (similiar to CutCopyMode = False).