Adding Value Axis Title to Graph

5 posts / 0 new
Last post
avikerem's picture
avikerem
Offline
Joined: 04/07/2015 - 00:28
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's picture
avikerem
Offline
Joined: 04/07/2015 - 00:28

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
 

Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57

Hi Avi, glad you got it working before I woke up :-)

dbreeden3
Offline
Joined: 01/09/2020 - 11:56

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!

Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57

Thanks for letting us know that the posting was helpful.