Change AutoShape Text Color in VBA?

[TheRobsterUK]

I have a button on a worksheet which runs some VBA code. Most of it
works fine but when it comes to a section that is supposed to change
the line, fill and font color of an AutoShape ( a rectangle) containing
some text, I have a problem. I can change the line color and fill color
okay with the following code:


Code:
--------------------
'fill color = tan
Shapes("test").Fill.ForeColor.SchemeColor = 47

'line color = black
Shapes("test").Line.ForeColor.SchemeColor = 64
--------------------


However, when I programmed in this code for the font color...


Code:
--------------------
Shapes("test").Font.ColorIndex = 0
--------------------


...I get the error message "object doesn't support this property or
method".

I take this to mean that you cannot change the font color, but this
must be wrong! How can I change the font color if the object doesn't
have the .Font.ColorIndex property?

If I actually select the object on the worksheet then I can record a
macro that will change the color. This is what I get:


Code:
--------------------
Selection.Font.ColorIndex = 0
--------------------


But I don't want to have to select the object in order to change the
font color.

What can I do?

Thanks
-Rob

 

[STEVE BELL]

I don't know how to do this in code - but the font color is set by "Fore
Color" for tool box buttons.

Got the following code by recording a Forms Toolbar button. You can delete
most of it and just get font color...

ActiveSheet.Shapes("Button 2").Select
Selection.Characters.Text = "Button 2"
With Selection.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3 ' <<< Red font
End With

--
steveB

Remove "AYN" from email to respond
"TheRobsterUK" <[email protected]>
wrote in message
news:[email protected]...

 

[Nick Hebb]

Note that macros don't always give you the full syntax since they
Select everything. If you want to change the color without selecting
the object then use the following:

Shapes("test").TextFrame.Characters.Font.ColorIndex = 0

The one problem that can occur is if there is no text in the Autoshape.
In that case the line above will throw an error (since there are no
characters). So it's best to precede it with an On Error Resume Next
statement.

HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

 

[TheRobsterUK]

Nick,

That works great, thanks! I've been tearing my hair out for hour
trying to figure out how to change the font colour. You're a lif
saver.

Cheers
-Ro