Change Text of Textbox in embedded chart

  • I have an embedded chart on Sheet1 named "Zn 10" with two custom textboxes on it. What I am trying to do is update the textbox values when the data is altered. I tried searching around the interwebs to figure this out but nothing I've found seems to work. This is all I have so far but it keeps throwing a 483 error "Object doesn't support this property or method" any help would be greatly appreciated.


    [VBA]Sub Macro1()


    Sheet1.ChartObjects("Zn 10").Shapes("Zn UCL").TextFrame.Characters.Text = "test"

    End Sub[/VBA]

  • Do you need a macro? If not, select the box, and in formula bar type equal sign and click the cell or type the named range with value you want in it.


    If you needed a macro, change chart name and textbox names to suit:

    Code
    1. Sheet1.ChartObjects("Chart 1").Chart.Shapes("TextBox 1").TextFrame2.TextRange.Text = "Hello World!"
  • Thanks Ken, that did the trick, I kept skipping over the .Chart member of the chart object but with your aid it is working now.


    I do need a macro as I am building an automated control chart sheet with multiple parameters, while trying to keep it looking tidy.


    Just out of curiosity, why is it called TextFrame2, is there a TextFrame1?

  • Excel does as I do for some of my naming conventions. TextFrame, TextFrame2, etc. Each method has other methods and properties specific to that method. TextFrame would be used to set alignment for example.


    Intellisense will give you a "sense" for what methods and properties are available after the period. e.g.

    Code
    1. Sheet1.shapes(1).


    I normally test in VBE's Immediate window for such things.


    I like to set up a cell to dynamically build the strings for such so I used the cell formula method more often. You can set that via VBA code but it is redundant since it is so easy to do manually and then it automates well.