[RESOLVED] Excel Chart Textbox with Formula

[SRVDisciple]

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

        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

[westconn1]

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

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

[SRVDisciple]

Sorry,

That doesn't work.

[incidentals]

what is the problem with your method?

here to think

[SRVDisciple]

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?

[westconn1]

try

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

i tested this

[SRVDisciple]

westconn1,

Beautimus!
Thank you!

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

[westconn1]

i checked in the locals window

[SRVDisciple]

pete aka westconn1.
Thanks again!