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

add axis and title

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

ResolvedVersion 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:

Display Formulas Instead of Results in Excel 2010

By pressing Ctrl ~ once, Excel will display formulas rather than the results of the formulas. Press it again, and the results will appear again.

A much quicker and simpler way of displaying your formulas!

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.