VBA: changing rotation of 3d clustered bar chart

[Wayne L.]

Hi Folks,

I was recently upgraded from Excel 2003 to 2010. I have some code that creates a 3d clustered bar chart, previously the chart's rotation was 0 and now it seems like Excel wants it to default to 15 degrees. I'd like to change the rotation back to zero, but Excel doesn't seem to see the chart.

I recorded a macro, inserted the relevant recorded code in my routine, but Excel doesn't seem to like it.

Here is the pertinent code (sorry for the copy/paste):

' begin code ------------------------------------------------
Dim Rng As Range
Dim cTyp As XlChartType

Set Rng = Range(Cells(2, 1), Cells(11, 2))
cTyp = xl3DBarClustered

Charts.Add

With ActiveChart
.ChartType = cTyp
.SetSourceData Source:=Rng, PlotBy:=xlColumns
.Location _
Where:=xlLocationAsNewSheet, _
Name:="Top 10"

.HasTitle = True
.ChartTitle.Characters.Text = "Top 10" & Chr(10) & "Dec 12"

.HasLegend = False

' two new lines added: the second results in error
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ThreeD.RotationX = 0

.ChartTitle.Top = 50

.Deselect
End With
' end code -------------------------------------------------

I'm getting a Visual Basic error that says "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found."

Excel seems to know the chart name, but doesn't seem to think there are any shapes (initially, a loop looking at the names of shapes in the shapes collection returned nothing; printing ActiveSheet.Shapes.Count returns zero.)


What is the correct method of rotating a 3d clustered bar chart?

(We're still using Windows XP here.)

Thanks in advance for any advice.

Wayne

 

[p45cal]

Not certain, but try:
.ThreeD.RotationX = 0
directly after:
.SetSourceData Source:=Rng, PlotBy:=xlColumns
and delete:
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ThreeD.RotationX = 0

 

[Wayne L.]

Not certain, but try:
.ThreeD.RotationX = 0
directly after:
.SetSourceData Source:=Rng, PlotBy:=xlColumns
and delete:
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 1").ThreeD.RotationX = 0

Thanks for the effort, ThreeD isn't a property of the ActiveChart and it didn't like it.

The issues seems to be more of Excel not seeing the Shapes object.

 

[p45cal]

have a play with:

Dim Rng As Range
Dim cTyp As XlChartType

Set Rng = Range(Cells(2, 1), Cells(11, 2))
cTyp = xl3DBarClustered

With Charts.Add
    .ChartType = cTyp
    .SetSourceData Source:=Rng, PlotBy:=xlColumns
    With .SeriesCollection(1).Format.ThreeD
       [COLOR=#0000ff] .FieldOfView = 0
        .RotationX = 10
        .RotationY = 90[/COLOR]
    End With
    .Location Where:=xlLocationAsNewSheet, Name:="Top 10"
    .HasTitle = True
    .ChartTitle.Characters.Text = "Top 10" & Chr(10) & "Dec 12"
    .HasLegend = False
    .ChartTitle.Top = 50
End With

 

[Wayne L.]

Awesome!! This worked wonderfully.

I just needed the .RotationX property and set the value to zero, but everything was here that I needed to complete the intended task.

I see what you did by consolidating the two previous "Chart.Add" and "With ActiveChart" lines -- I would not have done this intuitively, but I can see how you use(d) it and will be able to do similar tasks in the future.

Thanks so much for your input!!