2010 Access - how to add and work with shapes in Excel file

Question

I would like to add shapes and modify them from 2010 Access vba in an Excel worksheet.

The code I wrote based on Excel 'record macro' is:

StrSheetName = "Menu"
wkbModels.Sheets.Add(before:=wkbModels.Sheets("Models")).Name = StrSheetName
Set wksModelsMenu = wkbModels.Sheets(StrSheetName)

With wksModelsMenu
    iLeft = 1
    iTop = 1
    iWidth = 125
    iHeight = 200
    .Shapes.AddPicture fDirectory & "logo.jpg", False, True, iLeft, iTop, iWidth, iHeight
    iLeft = 240
    iTop = 1
    iWidth = 300
    iHeight = 125
    .Shapes.AddShape(msoShapeRoundedRectangle, iLeft, iTop, iWidth, iHeight).Select
    .Shapes(1).Range.ShapeStyle = msoShapeStylePreset10
    .Shapes(1).Range.TextFrame2.TextRange.Font.Bold = msoTrue
    .Shapes(1).Range.TextFrame2.VerticalAnchor = msoAnchorMiddle
    .Shapes(1).Range.TextFrame2.TextRange.Characters.Text = _
                "TEST TEST TEST"
    .Shapes(1).Range.TextFrame2.TextRange.Characters.ParagraphFormat.Alignment = msoAlignCenter
    .Shapes(1).Range.TextFrame2.TextRange.Characters.Font.Bold = msoTrue
End With

On the ".Shapes(1).Range.ShapeStyle..." and following ".Shapes(1)." statements I receive an error "Object doesn't support this property or method". Removing the "(1)" gives a compile error.

The mso library is installed and the referenced items (i.e., msoShapeStylePreset10) have the proper values.

Any ideas on what I need to do?

score 1 · Accepted Answer · answered May 07 '18 at 20:07

So, for what it's worth, I found that if I moved the index from after the "Shapes" to after the "Range" it works as needed.

This:

Shapes(1).Range.....

changes to:

Shapes.Range(1)

Of course if you look at the code, you see that the second shape is what I really want to work on.

Shapes(2).Range

did not work.

Shapes.Range(2)

worked perfectly.

score 1 · Answer 2 · answered May 07 '18 at 22:23

1

Shapes(1) returns a Shape object, which does NOT have a Range member, so Shapes(1).Range will generate an error. Removing Range from each line will likely fix your problem, for example Shapes(1).ShapeStyle = ...

Hope that helps.

answered May 07 '18 at 22:23

xidgel

2,832
2
10
22

Andre · Answer 3 · 2018-05-08T10:42:18.570

Worksheet.Shapes.AddShape() returns a Shape object.

It is better to use this object, instead of relying on the created shape being the second one on the sheet (which may change at some point).

This code (without .Range) works for me in Access 2010:

Dim shp As Excel.Shape

' Note: there is no reason to select the shape
Set shp = wksModelsMenu.Shapes.AddShape(msoShapeRoundedRectangle, iLeft, iTop, iWidth, iHeight)
' Use the created object to set its properties
With shp
    .ShapeStyle = msoShapeStylePreset10
    .TextFrame2.TextRange.Font.Bold = msoTrue
    .TextFrame2.VerticalAnchor = msoAnchorMiddle
    .TextFrame2.TextRange.Characters.Text = "TEST TEST TEST"
    .TextFrame2.TextRange.Characters.ParagraphFormat.Alignment = msoAlignCenter
    .TextFrame2.TextRange.Characters.Font.Bold = msoTrue
End With

You can also do it without a Shape variable (but I find the above code better readable):

With wksModelsMenu.Shapes.AddShape(msoShapeRoundedRectangle, iLeft, iTop, iWidth, iHeight)
    .ShapeStyle = msoShapeStylePreset10
    ' etc.
End With

Additional note:
You can do the same when creating the sheet:

Set wksModelsMenu = wkbModels.Sheets.Add(before:=wkbModels.Sheets("Models"))
wksModelsMenu.Name = StrSheetName

Yes, that's correct that the hard coded "2" may not be the new shape. To over come this, after the add, I have: i=ws.shapes.count and then use i as the index. So far I have been able to add many shapes with this and it's working fine. — LEBoyd, May 08 '18 at 13:09
Ok. But the above is really the best way to address newly created objects. `Set myBar = foo.CreateBar()` is very often applicable in the Office object model. @LEBoyd — Andre, May 08 '18 at 13:20

2010 Access - how to add and work with shapes in Excel file

3 Answers3

Linked