How do I anchor the axis title to the x-axis?

Question

After reversing the number order on the y-axis, and setting the x-axis to cross at 1 instead of at 0, the x-axis title is no longer next to the axis:

Is there any way to anchor the axis title to the axis, ideally in the white area below it? Searching the properties for axis-title, I don't find anything about where it is positioned.

Answer 1

It is not possible without using VBA, but with VBA it can be automated to trigger on any chart changes. This code assumes the chart is an embedded chart (embedded in a worksheet), as opposed to a chart sheet.

1. Select the Developer tab (instructions if it is not shown).
2. Select Editor.
3. In the Project window right-click anywhere and select Insert, Module.
4. With the new Module window selected press F6 (or click View then Properties Window).
5. Change the Name to ChartEventModule, and close the Properties window.
6. Paste the following code into the ChartEventModule window:

ChartEventModule code:

Option Explicit

Dim chartEventClassModule As New chartEventClassModule
Private Const chartSheet = "Sheet1"
Private Const chartNumber = 1

Sub RecalculateXAxisTitlePosition()
    Dim chart As chart, plot As PlotArea, axis As axis, title As AxisTitle, titleXPos As Double, titleYPos As Double

    Set chart = Worksheets(chartSheet).ChartObjects(chartNumber).chart
    Set plot = chart.PlotArea
    Set axis = chart.Axes(xlCategory) ' xlCategory is X axis, xlValue is Y axis

    ' If the X axis doesn't have a title, exit out
    If Not axis.HasTitle Then Exit Sub

    Set title = axis.AxisTitle

    ' Not necessary to set font size and text each time
    title.Text = "Verknadsgrad"
    title.Font.Size = 12

    ' .Position can be xlChartElementPositionAutomatic (-4105) or xlChartElementPositionCustom (-4114)
    title.Position = xlChartElementPositionCustom

    ' Without a title the plotarea top is 9
    ' With a title font size 12 the title height is 17.4, and the plotarea top is 26.4
    plot.Top = 9

    ' The PlotArea includes the axes.  The Y axis throws off our centering if we center based on the PlotArea.
    titleXPos = axis.Left + (axis.Width / 2) - (title.Width / 2)
    titleYPos = plot.Top + plot.Height

    title.Left = titleXPos
    title.Top = titleYPos

    ' OPTIONAL CODE
    ' This code sets the background fill to DiagonalDown, which ensures it goes exactly from corner to corner
    With plot.Format.Fill
        .Visible = msoTrue
        .TwoColorGradient Style:=msoGradientDiagonalDown, variant:=1
        ' Green color
        .ForeColor.RGB = RGB(0, 176, 0)
        ' Red color
        .BackColor.RGB = RGB(255, 0, 0)
        ' Percentage to be fully red (<=15%)
        .GradientStops(1).Position = 0.15
        ' Percentage to be fully green (>=85%)
        .GradientStops(2).Position = 0.85
        ' Middle color (yellow), center point (50%), Transparency (0%), Point to insert into list of gradients (1)
        .GradientStops.Insert RGB(255, 255, 0), 0.5, 0, 1
    End With
End Sub

Sub Initialise()
    Set chartEventClassModule.myChart = Worksheets(chartSheet).ChartObjects(chartNumber).chart
End Sub

I didn't like that when I set up the gradient I had to set a static angle, so I set it in code. It is set to DiagonalDown which automatically calculates the angle depending on the aspect ratio of the chart.

7. Make sure to set the 2 constants chartSheet and chartNumber appropriately at the top of the code.
8. Back in the Project window, right-click and create a Class Module.
9. Using the same Properties Window (F6), set the name to ChartEventClassModule.
10. Paste the following code into the ChartEventClassModule window:

ChartEventClassModule code:

Option Explicit

' https://msdn.microsoft.com/VBA/Excel-VBA/articles/using-events-with-embedded-charts
Public WithEvents myChart As chart

Private Sub myChart_Calculate()
    Call ChartEventModule.RecalculateXAxisTitlePosition
End Sub

Private Sub myChart_Resize()
    Call ChartEventModule.RecalculateXAxisTitlePosition
End Sub

Private Sub myChart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)
    Call ChartEventModule.RecalculateXAxisTitlePosition
End Sub

This code detects changes to the chart and re-runs the function to set the axis title. Unfortunately we are limited in the Chart events available.

11. In the Project window double-click ThisWorkbook, and paste the following code:

ThisWorkbook code:

Private Sub Workbook_Open()
    ChartEventModule.Initialise
End Sub

12. Save the workbook as a macro-enabled workbook (.xlsm). Close and re-open the workbook.

Presto! Now on any changes to the chart data, series, or size will trigger an event that causes it to recalculate the axis title position: