Excel 2007 SP1 VBA Help

[Wheeley]

I have created a WortArt object in Excel programatically using VBA and using
the preset text effect style msoTextEffect3. Unfortunately, this creates text
with the color red. Now I can right click on the word art and change the
color of the text to whatever I want. But I cannot find a way to do this
programmatically in VBA. Also, trying to use Macro recording is useless for
this. It looks like macro recording is broken in Excel 2007.

Any help grateful
Wheeley

 

[Tom Hutchins]

I just recorded myself in XL2003 creating a WordArt object (with
msoTextEffect3), then changing the text color to dark green. You already have
the code to create the WordArt, but maybe some of the other code generated
will be useful to you:

ActiveSheet.Shapes.AddTextEffect(msoTextEffect3, "Your Text Here", "Impact", _
36#, msoFalse, msoFalse, 261#, 169.5).Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoFalse

The Selection.ShapeRange.Fill.ForeColor.SchemeColor line is the one you are
probably most interested in. Since I didn't change anything else, the other
statements are probably repeating default values.

Hope this helps,

Hutch

 

[Wheeley]

This doesn't help. Excel 2007 has a completely different structure for
WordArt. What you did in Excel 2003 creates a green shadow box under the
WordArt in Excel 2007. Anyone else know how to change the text color?

Thanks for trying
Wheely

 

[Jon Peltier]

I can't answer your question, especially in the absence of any useful
recorder capabilities or help content for Excel 2007 shapes. However, what I
would do is root around in the new shapes object model using the object
browser and immediate window, until I figured out which commands made the
changes I needed.

- Jon

 

[Wheeley]

That is exactly what I have been doing and everything I try doesn't work. At
this point it looks like something that is NOT available in VBA. I've looked
through the object browser AND an instance variable when a script is running.
I just don't see the particular color attribute I am looking for. Imhave
plugged values into all of them to no success.

Wheeley

 

[Jon Peltier]

I appreciate your frustration. The new formatting paradigm for the new
shapes is not clearly documented, and I have only subjected myself to very
limited sessions trying to figure it out.

- Jon

 

[Tim vL]

This may help:-

Sub WordartEffects()
'create a wordart object, then...
Dim shp As Shape

Set shp = shtReport.Shapes("Rectangle 2550")
With shp
.TextEffect.Text = "abc"
' .TextEffect.PresetTextEffect = msoTextEffect1
.TextFrame.Characters.Font.ColorIndex = 4
End With
End Sub