Error when inserting AutoShape ????????

[Les Stout]

Hi, i have the code below that works perfectly in my Personal VBA
project, but the will not work in another VBA Project and i get a
compile error msg "Expected function or variable"
and then highlites "selection" of the second line ??? Can anybody help,
me out of my misery please ??

Sub InsertFlash01()
'
'
ActiveSheet.Shapes.AddShape(msoShapeExplosion2, 407.25, 162#, 525#,
409.5) _
.Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 13
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.Characters.Text = "THERE ARE NO NEW Gams DOCUMENTS TO BE
SHOWN"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = False
End With
With Selection.Font
.Name = "BMWTypeRegular"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1").Select
End Sub




Les Stout

 

[Peter T]

Try pasting the macro from the project in which it works over the macro that
fails.

Regards,
Peter T

 

[John Coleman]

Try this:

Sub InsertFlash01()
'
'
Dim myShape As Shape
Set myShape = ActiveSheet.Shapes.AddShape(msoShapeExplosion2,
407.25, 162#, 525#, 409.5)
myShape.Fill.ForeColor.SchemeColor = 13
myShape.Fill.Visible = msoTrue
myShape.Fill.Solid
myShape.TextFrame.Characters.Text = "THERE ARE NO NEW Gams
DOCUMENTS TO BE SHOWN "
With myShape.TextFrame
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ReadingOrder = xlContext
.Orientation = msoTextOrientationHorizontal
.AutoSize = False
End With
With myShape.TextFrame.Characters.Font
.Name = "BMWTypeRegular"
.FontStyle = "Bold"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1").Select
End Sub

All sorts of wierd implicit type-casting was going on with the
Selection - replacing it with a shape variable revealed that you were
not using the shape object-heirarchy correctly. I hope thisworks f0r
you. Remember to remove stray line-breaks from google.

-John Coleman

 

[Les Stout]

Thank you so much John, works great now.

Les Stout

 

[John Coleman]

Les,
I'm glad that it worked out for you. Still - it is a complete mystery
to me what was happening to you earlier. When I pasted your code into
Excel it worked right away with no problem. "Selection" seems to be
under-documented. In the first line of your code Selection seems to be
set to point to a shape object - but then later Selection.Font (for
example) is used even though shapes don't have fonts. It looks like
your code came from editing a recorded macro (which tends to do
everything via Selection). In this case the code the macro-recorder
generated seems counterintuitive. Did you edit-out some Selects? Maybe
when you entered the text into the shape the Selection changed
implicitly but this change left no footprint in the recorded code.
Still - why it would run in one project but not another is beyond me.
Maybe it has to do with what the Active Window is when you run the code
in the second project.

-John Coleman