Can't apply 3d properties to autoshape

Question

The workbook I am working on in Excel uses graphics with 3d properties which are set in VBA. I just changed an image in the workbook, and now Excel complains that it can't execute any of the following lines of code:

Selection.ShapeRange.ThreeD.Depth = fsdoord
Selection.ShapeRange.ThreeD.ExtrusionColor.RGB = carcol
Selection.ShapeRange.ThreeD.PresetLightingDirection = msoLightingBottom

The error produced is

Run-time error '70':

Permission denied

I can still apply all the other properties to this shape; it only errors when I try to execute the lines relating to the 3d properties.

Does anyone have any insight into why this might be happening?
Thanks in advance...

Answer 1

ShapeRange is actually a collection of Shapes. You need to drill down one more level to get the shape object. (As the properties you are trying to use apply to Excel.Shape.)

    Sub Example()
    Dim ws As Excel.Worksheet
    Dim shp As Excel.Shape
    Set ws = Sheet1
    Set shp = ws.Shapes(1)
    shp.ThreeD.Depth = 10.5!
    shp.ThreeD.ExtrusionColor.RGB = &HF08300
    shp.ThreeD.PresetLightingDirection = msoLightingBottom
End Sub
Sub Example2()
    Dim shpRng As Excel.ShapeRange
    Dim shp As Excel.Shape
    Set shpRng = Selection.ShapeRange
    Set shp = shpRng.Item(1)
    shp.ThreeD.Depth = 25.5!
    shp.ThreeD.ExtrusionColor.RGB = &HFF0083
    shp.ThreeD.PresetLightingDirection = msoLightingBottom
End Sub

Answer 2

There's a remark on the Help page for the ThreeDFormat object and it's repeated on the MSDN page here

You cannot apply three-dimensional formatting to some kinds of shapes, such as beveled shapes or multiple-disjoint paths. Most of the properties and methods of the ThreeDFormat object for such a shape will fail.

So if your new shape falls into one of those rather sketchily-defined categories then that might be your problem

Answer 3

Okay, I sort of found the problem. When I replaced the image, I deleted the original image and inserted a new one using Insert>Image>From File... It turns out that the original image must have had some sort of 3d properties applied already, because when I replaced the image by just changing the fill colour of the auto-shape, it worked (in other words, I kept all the properties and only changed the fill).

Answer 4

Dear All,

This is a question for you to help.

I am trying the change a color of a rectangle shape using drop down. If I put a cell the macro is working but with the shape it is not. Any help!

Private Sub dropdown4() Dim x As Integer x = Range("j5").Value With Sheets("sheet1").Shapes("rectangle 2").Select Select Case x Case 1 Selection.ShapeRange.Fill.ForeColor.RGB = 3 Case 2 Selection.ShapeRange.Fill.ForeColor.RGB = 3 Case Else Selection.ShapeRange.Fill.ForeColor.RGB = 2 End Select End With End Sub

Many thanks Nasser.