Kinga has attended:
Excel VBA Intro Intermediate course
Add axis and title to chart
Hello,
I managed to write a piece of code to standartize graphs, applying the same formatting according to our guidelines. I also wanted it to pick up axis and chart titles from the data workbook. The sub works great on it's own but when I add it in the main sub it's being skipped. Any ideas?
See code below.
Thanks!
Sub Main()
Theme
AddAxisAndTitles
GraphStandardise
AddSource
SaveAs
End Sub
'this sub applies the Islington theme
Sub Theme()
ActiveWorkbook.ApplyTheme ("\\xxxx.thmx" _)
End Sub
'this sub adds axes and titles
Sub AddAxisAndTitles()
On Error Resume Next
C_Count = ActiveSheet.ChartObjects.Count
For i = 1 To C_Count
With ActiveChart
.HasTitle = True
.ChartTitle.Text = Range("a1")
.Axes(xlValue).HasTitle = True
.Axes(xlCategory).HasTitle = True
End With
With ActiveChart.Axes(xlValue)
.AxisTitle.Text = Range("a2")
.SetElement (msoElementPrimaryValueAxisTitleRotated)
End With
With ActiveChart.Axes(xlCategory)
.AxisTitle.Text = Range("a3")
.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
End With
With ActiveChart.ChartTitle
.SetElement (msoElementChartTitleAboveChart)
.SetElement (msoElementChartTitleCenteredOverlay)
End With
Next
End Sub
'this sub applies all visual guidelines to the graph: border, font size, axis min and max, bar spacing, legend fill and overlay
Sub GraphStandardise()
On Error Resume Next
C_Count = ActiveSheet.ChartObjects.Count
For i = 1 To C_Count
ActiveSheet.ChartObjects(i).Select
With ActiveChart
.ChartArea.Border.LineStyle = xlNone
.Legend.Border.LineStyle = xlNone
.Legend.Interior.Color = RGB(255, 255, 255)
.Legend.SetElement (msoElementLegendRightOverlay)
.Axes(xlValue).MinimumScale = 0
.Axes(xlValue).MaximumScale = 1
.ChartArea.AutoScaleFont = False
End With
With ActiveChart.ChartArea.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With
With ActiveChart.Axes(xlCategory).AxisTitle.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
.Bold = True
End With
With ActiveChart.Axes(xlValue).AxisTitle.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
.Bold = True
End With
With ActiveChart.ChartTitle.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
.Bold = True
End With
With ActiveChart.Legend.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
End With
With ActiveChart.Axes(xlCategory).TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = 0
'if you need to change to Rotate by 270 degrees orientation change the value to 90
'.Orientation = 90
End With
If ActiveChart.ChartType = xlColumnClustered Or _
ActiveChart.ChartType = xlColumnStacked Or _
ActiveChart.ChartType = xlColumnStacked100 Or _
ActiveChart.ChartType = xlBarClustered Or _
ActiveChart.ChartType = xlBarStacked Or _
ActiveChart.ChartType = xlBarStacked100 Then
ActiveChart.ChartGroups(1).GapWidth = 80
End If
Next
End Sub
'this sub adds source; remeber to put it in your A4 cell or remove this sub
Sub AddSource()
With ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, Left:=1, Top:=1500, Width:=300, Height:=10).TextFrame
.Characters.Text = ActiveSheet.Range("A4")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
End With
End Sub
'this saves the chart as a separate sheet
Sub SaveAs()
With ActiveChart
.Location Where:=xlLocationAsNewSheet, Name:="Cht_" & ActiveSheet.Name
End With
End Sub
RE: Add axis and title to chart
Hi King, thanks for your query. Comment out your error trap here:
Sub AddAxisAndTitles()
'On Error Resume Next
Then re-run and see if that triggers an error message and leads you to the problem.
You can just have one error trap in Sub Main, by the way, instead of in each subroutine.
Hope this helps,
Anthony