add axis and title

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Add axis and title to chart

Add axis and title to chart

resolvedResolved · Urgent Priority · Version 2007

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

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Switching Between Spreadsheets

As the Alt+Tab key switches between loaded applications or files, Ctrl+Tab switches between loaded or open Excel files. Hold down the Ctrl key until you have tabbed to the correct spreadsheet.

View all Excel hints and tips


Server loaded in 0.07 secs.