font.background in Excel2007

[ErikJan]

Have code that runs well in Excel 2003 but gives error in 2007/2010.

Code: Select all

        ActiveChart.Shapes("Text Box 1").Select
        With Selection
            .ShapeRange.Fill.ForeColor.SchemeColor = 22
            .ShapeRange.Fill.BackColor.SchemeColor = 23
            .ShapeRange.Fill.TwoColorGradient msoGradientHorizontal, 1
            .AutoScaleFont = False
            .Font.FontStyle = "Regular"
            .Font.Size = 10
            .Font.Background = xlOpaque
            .Characters(Start:=9, Length:=1).Font.Name = "Symbol"
            .Left = CornerX + 140 - .Width - 31    'correct for real width + add margin
        End With

Get an error in the .Font.Background line, what is wrong? "xlOpaque" exists (value = 3)...?

To add to this; I also see that ".ColorIndex = xlAutomatic" now makes my textbox font white/invisible where in Excel 2003 it was black. I thought not much had changed between 2003 and 2007 but this seems pretty significant...

[HansV]

I get the same error message for xlOpaque in Excel 2010, even if I change it to xlBackgroundOpaque (which is now mentioned in the help). I looks like you can't set the Background property any more even though it is mentioned in the object model.

Setting the ColorIndex of the font to xlAutomatic (or xlColorIndexAutomatic) still makes the font black when I try it.

[ErikJan]

OMG... this is messy... here's another problem... (mind you: this worked great in 2003!)

I have a chart and want to add a text box with text in the upper right corner of the plot area, not only do I get an error on the "xlOpaque", but also the ".width" in the line below is ZERO and thus no longer reflects the real length of the text box (which depended on the variable text content)... now what?

Code: Select all

        ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 0#, 0#) _
                .TextFrame.Characters.Text = TextVariable
        ActiveChart.Shapes("Text Box 1").Select
        With Selection
            .ShapeRange.Fill.ForeColor.SchemeColor = 22
            .ShapeRange.Fill.BackColor.SchemeColor = 23
            .ShapeRange.Fill.TwoColorGradient msoGradientHorizontal, 1
            .AutoScaleFont = False
            .Font.FontStyle = "Regular"
            .Font.Size = 10
            .Font.Background = xlOpaque
            .Characters(Start:=9, Length:=1).Font.Name = "Symbol"
            .Left = CornerX + 140 - .Width - 31    'correct for real width + add margin
        End With

[HansV]

You set the width and height of the label to 0, so it's not surprising that .Width returns 0.

[ErikJan]

I don't see where I do that... again, this works OK in 2003!

[HansV]

In the line

ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 0#, 0#)

Left = CornerX
Top = CornerY
Width = 0#
Height = 0#

[ErikJan]

Yep, but in the next line I add text in that box (which changes the size obviously).

Maybe it doesn't make sense but it works in 2003!

[HansV]

In Excel 2010, setting the text does not change the width, and I assume it's the same in Excel 2003

[ErikJan]

OK, so two questons then:
1. Why does this work in 2003?
2. How do I create a text box, put variable text into it and then right align it on my plotwindow (as I can set only left and top, I need to know the right margin position and the width of the text box to be able to calculate the LEFT)

Right?

[HansV]

You could use something like this:

Code: Select all

    ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 0#, 0#).Select
    With Selection
        .ShapeRange.TextFrame.Characters.Text = TextVariable
        .ShapeRange.TextFrame.AutoSize = msoAutoSizeShapeToFitText ' This resizes the shape
        .ShapeRange.Fill.ForeColor.SchemeColor = 22
        .ShapeRange.Fill.BackColor.SchemeColor = 23
        .ShapeRange.Fill.TwoColorGradient msoGradientHorizontal, 1
        .AutoScaleFont = False
        .Font.FontStyle = "Regular"
        .Font.Size = 10
        .Characters(Start:=9, Length:=1).Font.Name = "Symbol"
        .Left = ActiveChart.Parent.Width - .Width - 31    'correct for real width + add margin
    End With