[RESOLVED] Excel Chart Textbox with Formula

I add a textbox in the lower right hand corner of a chart for supplemental information. This code snippet works:

Code:

---

        Set tb = ActiveChart.Shapes.AddTextbox _
                (msoTextOrientationHorizontal, _
                ActiveChart.ChartArea.width - 125, _
                ActiveChart.ChartArea.Height - 22, _
                124, _
                21)
       
        With tb
            .Fill.BackColor.RGB = RGB(225, 225, 225)
            .TextFrame.HorizontalAlignment = xlHAlignCenter
            .TextFrame.VerticalAlignment = xlVAlignCenter
        End With 'textbox "frame"
       
        tb.Select
        Selection.Formula = "='peak detect'!$I$2"

---

where "peak detect" is my source page for the formula. Look at the last two lines. Why do I have to do this? For some reason I have to select the shape object to set the formula. I can't find any other way to set the formula property of a textbox.

Thank you

i do not believe that textboxes have a formula property, possibly like

vb Code:

With tb
            .Fill.BackColor.RGB = RGB(225, 225, 225)
            .TextFrame.HorizontalAlignment = xlHAlignCenter
            .TextFrame.VerticalAlignment = xlVAlignCenter
            .textfame.range.text =  "='peak detect'!$I$2"
        End With

Sorry,

That doesn't work.

what is the problem with your method?

here to think

I'm just bamboozled that I have to "select" the textbox to assign it a formula. Why does the act of selecting suddenly give me access to the formula property?

try

vb Code:

tb.oleformat.object.formula =  "='peak detect'!$I$2"

i tested this

westconn1,

Beautimus!
Thank you!

PS - how did you find this? I've been mucking through the object model for days with no luck ...

i checked in the locals window

pete aka westconn1.
Thanks again!