Adding Axis Title in Excel with VBA

Question

Hello there,

I am trying to make a Macro for adding X and Y-Axis tile in Excel with VBA. However, I noticed that it only allows me add title of X-Axis but not Y-Axis. Eventually, tile of X Axis will be replaced by Y. I am not sure what's wrong with it. The following is the code. Please help me figure out what is going on. Thanks in advance.

ActiveSheet.ChartObjects("Chart 2").Activate  
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleNone) ' clear out whatever it has  
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleNone) ' clear out whatever it has  
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)  
Selection.Caption = "X-Axis"  
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)  
Selection.Caption = "Y-Axis"

Answer 1

I suggest that using the Axis object as follows.

Public Sub Sample()  
  With ActiveChart  
    With .Axes(xlCategory, xlPrimary)  
      .HasTitle = True  
      .AxisTitle.Text = "X"  
    End With  
    With .Axes(xlValue, xlPrimary)  
      .HasTitle = True  
      .AxisTitle.Text = "Y"  
    End With  
  End With  
End Sub