I have a function that gets called from the Worksheet_Change event.
Based on a set of conditions it adds a shape to the worksheet using a
function like this:

Set newShape = ws.Shapes.AddShape(shapeType, l, t, w, h)

where ws is a Worksheet object and shapeType is a msoAutoShapeType
variable.

In general it works fine. But while doing some testing I came across a
strange (to me) error. If I add a number of shapes to the worksheet,
then delete one, subsequent calls to this function raise an error.

The error is 434 - object required.

Here's the weirdness:
- The shape gets properly created
- Debug.Assert Not(ws is Nothing) checks out fine
- Debug.Assert Not(ws.Shapes is Nothing) checks out fine
- Debug.Print ws.Shapes.Count spits out the proper shape count before
and after the line.

So I'm not sure which 'object' it's referring to since they all seem to
be there and are visible is the Locals window when debugging.

Any ideas?

Thanks,

Nick Hebb

Hi Nick,

I cannot recreate your error despite some vigorous testing (below). Wild
guess, have you been renaming shapes with default type names that previously
existed. Eg, delete "AutoShape 1" and rename another as "AutoShape 1".
Although I can't directly relate to your issue, doing that can cause
unexpected problems.

Regards,
Peter T


Function fNewShape(ws As Worksheet, _
shapeType&, l#, t#, w#, h#) As Boolean
Dim newShape As Shape

Set newShape = ws.Shapes.AddShape(shapeType, l, t, w, h)

End Function

Sub test()
Dim l#, t#, h#, w#
Dim i&, n&, nType&, nDel&
Dim ws As Worksheet

Set ws = ActiveSheet
ws.Cells.Interior.ColorIndex = 40
ws.DrawingObjects.Delete
l = 18: t = 6: h = 18: w = 36

For i = 1 To (5 * 137)
n = n + 1
nType = nType + 1
fNewShape ws, nType, l, t, w, h
t = t + 30
If nType = 137 Then
nType = 0
l = l + 90
t = 6
End If
If n = 5 Then
ws.Shapes(ws.Shapes.Count).Delete
nDel = nDel + 1
n = 0
End If
Next

MsgBox ws.Shapes.Count & vbCr & nDel & vbCr & i - 1
End Sub



"Nick Hebb" wrote in message
ups.com...
I have a function that gets called from the Worksheet_Change event.
Based on a set of conditions it adds a shape to the worksheet using a
function like this:

Set newShape = ws.Shapes.AddShape(shapeType, l, t, w, h)

where ws is a Worksheet object and shapeType is a msoAutoShapeType
variable.

In general it works fine. But while doing some testing I came across a
strange (to me) error. If I add a number of shapes to the worksheet,
then delete one, subsequent calls to this function raise an error.

The error is 434 - object required.

Here's the weirdness:
- The shape gets properly created
- Debug.Assert Not(ws is Nothing) checks out fine
- Debug.Assert Not(ws.Shapes is Nothing) checks out fine
- Debug.Print ws.Shapes.Count spits out the proper shape count before
and after the line.

So I'm not sure which 'object' it's referring to since they all seem to
be there and are visible is the Locals window when debugging.

Any ideas?

Thanks,

Nick Hebb