Axes(xlCategory) for a chart-sheet in 2007

  • Thread starter Thread starter Arkadiy
  • Start date Start date
A

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
 
Interestingly enough, in exactly the same situation, setting
..TickMarkSpacingIsAuto does work (but that's not really help me).
 
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,
Click to expand...

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
 
Back
Top