Axes(xlCategory) for a chart-sheet in 2007

[Arkadiy]

I am unsuccessfully trying to set TickLabelSpacing for X-axis in a
chart-sheet. Tries recording the steps into a macro, but the recorded code
returns an error when i attempt to run it myself.
The problem has something to do with the fact that the charts in
chart-sheets aren't embedded into ChartObjects, and setting TickLabelSpacing
directly for a chart object doesn't do anyhting.

Here's the recorded code (fails in 2007):

Sub Macro3()
'
' Macro3 Macro
'

'
Sheets("Chart").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).TickMarkSpacing = 12
ActiveChart.Axes(xlCategory).TickLabelSpacing = 12
End Sub

Removing ActiveSheet.ChartObjects("Chart 1").Activate will make excel
ignore TickLabelSpacing in 2007, and will actaully work fine in 2003

Could you please advise if I am doing something wrong or this is a known bug
(and in that case if a workaround exists)?

Thank you

 

[Arkadiy]

Interestingly enough, in exactly the same situation, setting
..TickMarkSpacingIsAuto does work (but that's not really help me).

 

[Peter T]

TickMarkSpacing and TickLabelSpacing both work fine for me in 2007

The problem has something to do with the fact that the charts in
chart-sheets aren't embedded into ChartObjects,

I don't follow if you are working with an embedded chart or a chart-sheet,
adapt the following as appropriate

Dim cht as Chart
' if a chart-sheet
Set cht = Sheets("Chart1") ' << change name

' or an embedded chart
Set cht = ActiveSheet.ChartObjects("Chart 1").Chart ' ' << change name
or
Set cht = Worksheets("Sheet1").ChartObjects("Chart 1").Chart

If cht Is Nothing then
msgbox "chart not assigned !"
Exit Sub
End If

With cht.Axes(xlCategory)
.TickMarkSpacing = 12
.TickLabelSpacing = 12
End With

(note no need to select either the sheet or the chart)

Regards,
Peter T