+ Reply to Thread
Results 1 to 2 of 2

Axes(xlCategory).TickLabels problem

  1. #1
    deko
    Guest

    Axes(xlCategory).TickLabels problem

    When I create a chart (manually or with automation) and define the
    xlCategory Axis with 2 columns, the TickLabels appear all scrunched together
    and are not readable. The TickLabel text is made up of a date value (from
    column A) and a time value (from column B):

    "02/27/05 06:47:24"

    If I make the chart really wide, both date and time appear horizontal like
    this:

    02/27/05
    06:47:24

    But the chart requires a Width of 500 which makes the time portion of the
    Ticklabel appear vertical (and readable), but the date portion appears
    horizontal and all scrunched together. I've tried changing the
    ..Axes(xlCategory).TickLabels.Orientation property, but the changes only
    apply to the time value (from column B).

    Is there a way to get both the date and time to appear vertically so the
    entire TickLabel is readable?

    Thanks in advance.


    Here is the macro code generated when creating the chart manually:

    Sub Macro1()
    Charts.Add
    ActiveChart.ChartType = xlLineMarkersStacked
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C22"), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
    .HasAxis(xlCategory, xlPrimary) = True
    .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
    ActiveChart.HasLegend = False
    ActiveChart.HasDataTable = False
    End Sub



  2. #2
    Jon Peltier
    Guest

    Re: Axes(xlCategory).TickLabels problem

    You can insert a new column (C) which adds A and B, giving the date and time as a
    single number (Excel stores dates as whole numbers and times as fractions of a day,
    so adding the two gives a time of a given day).

    Make your chart with column C as the category labels, then make sure the axis is a
    category, not date-scale, axis: From the Chart menu, choose Chart Options, click on
    the Axes tab, and under Category axis, select Category. This last step is important
    because a line chart with a time-scale axis ignores the time component, and uses
    just the date component for the X value, and all data for a given day is plotted at
    midnight.

    An alternative is to skip intermediate labels: double click the axis, and on the
    Scale tab, change the Number of Categories between Tick Mark Labels.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    deko wrote:
    > When I create a chart (manually or with automation) and define the
    > xlCategory Axis with 2 columns, the TickLabels appear all scrunched together
    > and are not readable. The TickLabel text is made up of a date value (from
    > column A) and a time value (from column B):
    >
    > "02/27/05 06:47:24"
    >
    > If I make the chart really wide, both date and time appear horizontal like
    > this:
    >
    > 02/27/05
    > 06:47:24
    >
    > But the chart requires a Width of 500 which makes the time portion of the
    > Ticklabel appear vertical (and readable), but the date portion appears
    > horizontal and all scrunched together. I've tried changing the
    > ..Axes(xlCategory).TickLabels.Orientation property, but the changes only
    > apply to the time value (from column B).
    >
    > Is there a way to get both the date and time to appear vertically so the
    > entire TickLabel is readable?
    >
    > Thanks in advance.
    >
    >
    > Here is the macro code generated when creating the chart manually:
    >
    > Sub Macro1()
    > Charts.Add
    > ActiveChart.ChartType = xlLineMarkersStacked
    > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C22"), _
    > PlotBy:=xlColumns
    > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    > With ActiveChart
    > .HasAxis(xlCategory, xlPrimary) = True
    > .HasAxis(xlValue, xlPrimary) = True
    > End With
    > ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
    > ActiveChart.HasLegend = False
    > ActiveChart.HasDataTable = False
    > End Sub
    >
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1