Accessing Wordart Styles Thru VBA

[CaliKidd]

I'm using Excel 2007 and I've learned that the macro recorder doesn't work when trying to format Wordart styles. It's my understanding that Microsoft didn't have time to get everything hooked up before releasing this Office version...

Any ways, can someone tell me what the VBA code would be to access the Wordart Style presets? I've been looking through the object model, but I can't seem to find them.

To be clear what I am referring to, from the Format menu, the 30 presets are in a section called "Wordart Styles." Next to the three A's there is a drop-down. When you click it, it'll display 20 presets for selected text and 10 presets for all text.

Also, to the right of the three A's, there are dropdowns for Text Fill, Text Outline, and Text Effects. I'd like to know how to access those through VBA too.

Thank you.

 

[CaliKidd]

If anyone has Excel 2003 or 2010, could you create a simple Wordart object, then turn on the macro-recorder to record selecting the various Wordart styles and text effects presets in order to see what the code looks like? Thank you.

 

[pgc01]

Hi

See if this helps:

Sub Test()
Dim shpWA As Shape
 
Set shpWA = ActiveSheet.Shapes.AddTextEffect( _
    PresetTextEffect:=msoTextEffect18, _
    Text:="My test", _
    FontName:="Arial Black", _
    FontSize:=54, _
    FontBold:=msoFalse, _
    FontItalic:=msoFalse, _
    Left:=50, _
    Top:=50)
        
' change style
shpWA.ShapeStyle = msoShapeStylePreset2
    
With shpWA.TextFrame2.TextRange
    
    ' Change font
    .Font.Name = "Arial"
    .Font.Size = 80
    
    ' Change font colour
    .Font.Fill.ForeColor.RGB = vbBlue
    
    ' Change text outline
    .Font.Line.ForeColor.RGB = vbRed
    
    ' Add shadow
    .Font.Shadow.Type = msoShadow29
End With

 

[CaliKidd]

Thank you. I stepped through the code and it works, so now I'll experiment with it. I appreciate you taking the time to help out a fellow man.