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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

...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:

Please Login or Register  to view this content.

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

What can I do?

*Edit* I should also mention that the code is within a worksheet code module, not a standard code module.

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" <TheRobsterUK.1w6u6d_1128110729.8512@excelforum-nospam.com>
wrote in message
news:TheRobsterUK.1w6u6d_1128110729.8512@excelforum-nospam.com...
>
> 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
>
>
> --
> TheRobsterUK
>
>
> ------------------------------------------------------------------------
> TheRobsterUK's Profile:
> http://www.excelforum.com/member.php...fo&userid=9924
> View this thread: http://www.excelforum.com/showthread...hreadid=472204
>

[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 hours trying to figure out how to change the font colour. You're a life saver.

Cheers
-Rob