none
Rotate DataLables in Excel chart Series

    Question

  • hi guys,

    if anyone can halp me in this problem, i try to change DataLable of Mycharts Series view to VerticalAlignmen.

      Dim oChart As Excel.Chart
    
    With oChart
          
          Dim chartRange As Excel.Range
          chartRange = osheet.Range("A3", "C" & R)
          .SetSourceData(chartRange)
          .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3
          .PlotBy = Excel.XlRowCol.xlColumns    .ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue)
          .HasLegend = True
          .Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight
          .HasTitle = True
          .ChartTitle.Text = "Employee Evaluation"
          Dim xlAxisCategory, xlAxisValue As Excel.Axes
          xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
          xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
              xlAxisCategory.Item(Excel.XlAxisType.xlCategory).Border.Color = Color.Pink.ToArgb
          'set the xlAxisCategory name
          xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Work Day"
          'select the axis lable Orientation
          xlAxisCategory.Item(Excel.XlAxisType.xlCategory).TickLabels.Orientation = 45
          xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
          xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
          xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Employee"
          'set the size of font into chart
          .ChartArea.Font.Size = 16
          'set font to bold
          .ChartArea.Font.Bold = True
    
        End With
    

    i use Microsoft office 2007

    and vb.net 2008

    Happy new year

    • Moved by Cindy Meister MVPMVP Sunday, January 02, 2011 6:28 AM Excel-related question (From:Visual Studio Tools for Office)
    Sunday, January 02, 2011 6:26 AM

Answers

  • when solving these type problems I usually first try to get the answer using Excel VBA and then converter the macro I get working to visual Studio (VB, C#, or C++).  I know if a get the code working in VBA I can also get it working in visual Studio.

    I created on my Worksheet a very simple chart.  The using the mouse looked at the Chart Option - Data Labels and found that Date Labels only have the following properties

    • Series Name
    • Category Name
    • Value

    dThe was no orientation.  So assumed you wanted to change the Orientation of the Tick Labels on the Xaxis to vertical.  It is the only option that you can change to a vertical orientation.  I then recorded a VBA macro while changing the orinetation to vertical.

    I modified the recorded macro to remove "selection" and create real objects.  A chart on a worksheet is really a ChartObject (the shape) with a plot area called a Chart .  the chart has an X-Axis and y-Axis which have tick marks.  The Tick Mark orientation can be changed to any angle from 0 to 90 degrees.

     

    This is the VBA code I got.  VBnet should be the same except you don't use the word SET in the instructions.

    Set ChartObj = Sheets("sheet1").ChartObjects("Chart 1")
    Set oChart = ChartObj.Chart
    Set xAxis = oChart.Axes(xlCategory)
    With xAxis.TickLabels
       .Orientation = xlUpward
    End With

     


    jdweng
    • Proposed as answer by Bruce Song Thursday, January 13, 2011 2:36 AM
    • Marked as answer by Bruce Song Thursday, January 13, 2011 10:31 AM
    Sunday, January 02, 2011 10:00 AM

All replies

  • when solving these type problems I usually first try to get the answer using Excel VBA and then converter the macro I get working to visual Studio (VB, C#, or C++).  I know if a get the code working in VBA I can also get it working in visual Studio.

    I created on my Worksheet a very simple chart.  The using the mouse looked at the Chart Option - Data Labels and found that Date Labels only have the following properties

    • Series Name
    • Category Name
    • Value

    dThe was no orientation.  So assumed you wanted to change the Orientation of the Tick Labels on the Xaxis to vertical.  It is the only option that you can change to a vertical orientation.  I then recorded a VBA macro while changing the orinetation to vertical.

    I modified the recorded macro to remove "selection" and create real objects.  A chart on a worksheet is really a ChartObject (the shape) with a plot area called a Chart .  the chart has an X-Axis and y-Axis which have tick marks.  The Tick Mark orientation can be changed to any angle from 0 to 90 degrees.

     

    This is the VBA code I got.  VBnet should be the same except you don't use the word SET in the instructions.

    Set ChartObj = Sheets("sheet1").ChartObjects("Chart 1")
    Set oChart = ChartObj.Chart
    Set xAxis = oChart.Axes(xlCategory)
    With xAxis.TickLabels
       .Orientation = xlUpward
    End With

     


    jdweng
    • Proposed as answer by Bruce Song Thursday, January 13, 2011 2:36 AM
    • Marked as answer by Bruce Song Thursday, January 13, 2011 10:31 AM
    Sunday, January 02, 2011 10:00 AM
  • Hi Joel

    i think there is  miss understanding . i exactly want to change the display type value  that appeared from this code:

     oChart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue)

    in this code the value is display horizontally.I think you now Know what i want ?

    ___________________________________________________________________

    ""happy new year Joel""

    Sunday, January 02, 2011 6:02 PM
  • I said in my previous posting that the Data Labels don't have a rotation porperty.  they only have the properties

    • Series Name
    • Category Name
    • Value

    jdweng
    Monday, January 03, 2011 4:51 AM