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

TomHutchins
Fri May 09 10:47:01 CDT 2008

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

Wheeley
Sat May 10 19:41:02 CDT 2008

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

Jon
Mon May 12 18:49:46 CDT 2008

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" <Wheeley@discussions.microsoft.com> wrote in message
news:50CB0BE9-EAE6-42F7-923C-771511B8671E@microsoft.com...
> 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

Wheeley
Mon May 12 21:40:00 CDT 2008

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
> _______
>

Jon
Tue May 13 06:32:32 CDT 2008

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" <Wheeley@discussions.microsoft.com> wrote in message
news:02E993FE-7B9A-4F72-A964-46D50F8AD56D@microsoft.com...
> 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
>> _______
>>