Hello everyone!
I am trying to make use of Jon Peltier's "Chart Creation" code:
https://peltiertech.com/Excel/Charts...tCreation.html
I copied the CreateChart procedure into my "Sheet1" and I was able to set the different ranges.
So far everything works, thank you, Jon!
Unfortunately, I can't work out, how to format the Axis.
Two things I'm trying to change:
1. Font
2. Show the vertical Axis without values and set a maximum value.
This is Jon's code :
Option Explicit
'==============================================================
Sub d1_CREATE_CHART_D()
Dim objChart As ChartObject
Dim myChtRange As Range
Dim myDataRange As Range
With ActiveSheet
' What range should chart cover
Set myChtRange = Range("B7:C23") ' SET
' What range contains data for chart
Set myDataRange = Range("B2:C3") ' SET
' Cover chart range with chart
Set objChart = .ChartObjects.Add( _
Left:=myChtRange.Left, Top:=myChtRange.Top, _
Width:=myChtRange.Width, Height:=myChtRange.Height)
' Put all the right stuff in the chart
With objChart.Chart
.ChartArea.AutoScaleFont = False
.ChartType = xlColumnClustered
.SetSourceData Source:=myDataRange
.SeriesCollection(1).Name = Range("B1") ' SET
.HasLegend = False
.HasTitle = False ' added by me, NO EFFECT?!
.SetElement (msoElementChartTitleNone) ' added by me, NO EFFECT?!
.SetElement (msoElementPrimaryValueAxisNone) ' added by me
.SetElement (msoElementPrimaryValueGridLinesNone) ' added by me
.HasAxis(xlCategory) = True ' added by me
' With .Axis(xlCategory).Select ' added by me
' Selection.Format.TextFrame2.TextRange.Font ' added by me
' .BaselineOffset = 0 ' added by me
' .Name = "Arial" ' added by me
' End With ' added by me
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
With .AxisTitle
.Characters.Text = "My X Axis"
.Font.Size = 10
.Font.Bold = True
End With
' .ShowValue = True ' added by me
End With
' With .Axes(xlValue, xlPrimary)
' .HasTitle = True
' With .AxisTitle
' .Characters.Text = "My Y Axis"
' .Font.Size = 10
' .Font.Bold = True
' End With
' End With
End With
End With
End Sub
'==============================================================
And this is the Macro I recorded:
Sub Macro_Axes_Font_Format_()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).Select
With ActiveChart.Axes(xlCategory).Select
With Selection.Format.TextFrame2.TextRange.Font
.BaselineOffset = 0
.Name = "Times"
.Size = 12
End With
End With
End Sub
The Macro causes an "Automation Error".
Many thanks in advance for any help!
Toni
Bookmarks