Skip to main content

Adding Value Axis Title to Graph

The following code, generated by Excel 2013 VB, with the nessassary changes , will not work: 
 
    wbOut.ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ex.Selection.Caption = "Month"
    wbOut.ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
  ex.Selection.Caption = "Revenue"
 
The Category Axis title is ok, however Idea will not let you add title to the Value Axis.
 I thought it has to do with the ex.selection statement, but even when I seperated the code to two distinctive parts, one for the X Axis and one for the Y Axis it will still add title to the Category Axis but not to the Value Axis:
 
    wbOut.ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ex.Selection.Caption = "Month"
wbOut.ActiveSheet.ChartObjects("Chart 1").Activate
wbOut.ActiveChart.Axes(xlValue).AxisTitle.Select
  wbOut.ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ex.Selection.Caption = "Revenue"
 

avikerem Mon, 04/04/2016 - 04:23

Ok, I found that the problem was that you must ensure the "HasTitle" attribute is set to True and just by coincidence it was True for the X Axis.
 
The full code that works is somthing like that:
    wbOut.ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ex.Selection.Caption = "Month"
  wbOut.ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
wbOut.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
wbOut.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Characters.Text = "Revenue"
wbOut.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat.TextDirection = msoTextDirectionLeftToRight
wbOut.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat.Alignment = msoAlignCenters
 

dbreeden3 Thu, 01/09/2020 - 12:01

I created an account just to say thank you for this clear, concise answer to this issue. I have spent days banging my head against other Excel VBA problems with the solution being hidden on the 18th page of search results (or no search results at all!) and this is the first time that I've found the solution immediately and it just worked.
Microsoft, if you're listening, fix your VBA builder!