X axis captions

[Stefi]

Hi All,

I have a macro creating a column chart. It takes category axis captions from
range("E1:AB1"), values from a selected row (row No is stored in a variable
"sourcerow"), so my VBA line is:

ActiveChart.SetSourceData Source:=Sheets(lapnev).Range( _
"E1:AB1,E" & sourcerow & ":AB" & sourcerow), PlotBy:=xlRows

It works well except that in some cases only every second caption is
displayed on the chart (E1 is displayed, F1 is left blank, G1 is displayed,
H1 is left blank, etc.). The value columns are correct above the blank
captions. I couldn't figure out any differences between correct and incorrect
cases.

How can I force to always display the whole caption range?

Thanks,
Stefi

 

[Andy Pope]

Hi,

This will force every category label to be displayed

ActiveChart.Axes(xlCategory).TickLabelSpacing = 1

Cheers
Andy

 

[Stefi]

Thanks Andy, my code already contained this line, but your post guided me to
search for a solution around TickLabels. Changing TickLabels.Font.Size from
10 to 8 solved my problem, though I still don't understand what caused the
different displays of the same content.

Regards,
Stefi

„Andy Pope†ezt írta:

 

[Stefi]

I checked again my charts and found that TickLabelSpacing was set to 2 in
charts displayed with every second ticklabels. Adding an extra
ActiveChart.Axes(xlCategory).TickLabelSpacing = 1
at the end of the macro also fixed the problem.

The original code was this:

With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlHorizontal
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = skalamax
.MinorUnit = 1
End With

Which statement after
.TickLabelSpacing = 1
changed TickLabelSpacing setting to 2?

Regards,
Stefi


„Stefi†ezt írta:

 

[Stefi]

Then it remains a secret of Excel mentality!
Thanks,
Stefi


„Andy Pope†ezt írta:

 

[Stefi]

Finally I've found out that in which cases Excel overrides the original
setting of Ticklabelspacing:
If the worksheet window is maximized within Excel window (no matter Excel
window is maximized or not) then it work OK in all cases.
If it's not then Excel resets Ticklabelspacing to 2 in some cases.

I still don't know what it depends on and why does it happen!

Stefi

„Stefi†ezt írta:

 

[Stefi]

Another thing:
ActiveChart.Axes(xlValue).MaximumScale = skalamax
and
ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 8
both made Excel recalculate Ticklabelspacing (but only if worksheet window
was not maximized).

Stefi

„Stefi†ezt írta: