Excel/VBA - change font color of object?

[TheRobster]

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:

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

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:

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?

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

Thanks
-Rob

[dannymking]

Does..

Shapes("test").Font.Color = 0

Not work??

[dannymking]

After a quick look into this there is no Font Object on the Shapes collection, you are going to habe to select it first as this is the only way you can change the font color..

At Least thats all I've found so far..

[TheRobster]

That appears to be the case but if so it's a bit stupid!

The object obviously has some kind of font colour property (how else would it know what font colour to display otherwise?) but I don't understand why there is no direct way to refer to it!

[malik641]

Found this with a little research. You don't have to select the shape to change the font.

I don't know why you would have to go through this route instead of what you were trying (because that seems like the LOGICAL way of performing this), but it works.

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

BAM!

[RobDog888]

You access it through the textframe property. You can declare a shape object for easier access to its properties.

Set oShp = ActiveSheet.Shapes("Rectangle 1")
    oShp.TextFrame.Characters.Font.ColorIndex = 5 'Change the rectangles text to blue

Edit: Doh! Should have refreshed the page before posting. Oh well, still shows another way to set the object.

[dannymking]

And here I was looking at TextEffect...

You don't have to use the ColorIndex with the font by the way you can specify the RGB values..

Here's another way.. refering to the Font Object

Dim fnt As Font
  Set fnt = ActiveSheet.Shapes("Rectangle 1").TextFrame.Characters.Font
  fnt.Color = RGB(123, 123, 0) 
  Set fnt = Nothing

[TheRobster]

Thanks for the help guys.

By the way, when you write the code as:

Set oShp = ActiveSheet.Shapes("Rectangle 1")
    oShp.TextFrame.Characters.Font.ColorIndex = 5 'Change the rectangles text to blue

And not as:

ActiveSheet.Shapes("Rectangle 1" _
.TextFrame.Characters.Font.ColorIndex = 5 'Change the rectangles text to blue

What's the main reason for declaring a variable rather than just using the actual object reference? Is the code faster or is it just 'tidier' to use a variable? I have tended to use the full object reference in the past but noticed that most of you declare variables wherever possible.

[malik641]

Thanks for the help guys.

By the way, when you write the code as:

Set oShp = ActiveSheet.Shapes("Rectangle 1")
    oShp.TextFrame.Characters.Font.ColorIndex = 5 'Change the rectangles text to blue

And not as:

ActiveSheet.Shapes("Rectangle 1" _
.TextFrame.Characters.Font.ColorIndex = 5 'Change the rectangles text to blue

What's the main reason for declaring a variable rather than just using the actual object reference? Is the code faster or is it just 'tidier' to use a variable? I have tended to use the full object reference in the past but noticed that most of you declare variables wherever possible.

Well...declaring variables will make VBA run faster as it doesn't have to do continuous checks to what variables should be dimensioned as.

But the difference is probably minimal....I would guess...and I said 'Guess'

I would declare that object only if I was going to perform more changes to it. If that's all you're going to do with that object (is change the font color) then I'd say don't worry about setting the variable.

...But that's just my opinion...