Axes(xlCategory).TickLabels problem

[deko]

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

 

[Jon Peltier]

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/
_______