Excel 2007 SP1 VBA Help
 

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

Excel 2007 SP1 VBA Help
 

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" wrote:

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

Excel 2007 SP1 VBA Help
 

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

"Tom Hutchins" wrote:

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" wrote:

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

Excel 2007 SP1 VBA Help
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Wheeley" wrote in message
...
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

"Tom Hutchins" wrote:

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" wrote:

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

Excel 2007 SP1 VBA Help
 

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Excel 2007 SP1 VBA Help
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Wheeley" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Excel 2007 SP1 VBA Help
 

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


*** Sent via Developersdex http://www.developersdex.com ***