Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when inserting AutoShape ????????
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when inserting AutoShape ????????
Try pasting the macro from the project in which it works over the macro that
fails. Regards, Peter T "Les Stout" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when inserting AutoShape ????????
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 wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when inserting AutoShape ????????
Thank you so much John, works great now.
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when inserting AutoShape ????????
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 Les Stout wrote: Thank you so much John, works great now. Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting an autoshape into a chart | Excel Discussion (Misc queries) | |||
error in inserting a column | Excel Discussion (Misc queries) | |||
Error message when inserting function | Excel Discussion (Misc queries) | |||
Error in FM20.dll when inserting userform | Excel Programming | |||
system error when inserting UserForms | Excel Programming |