Set textbox drawing object to cell reference in VBA RRS feed

  • Question

  • Likely a basic question.  Without VBA to link a textbox to a cell you can directly select the text box and enter the cell reference in the formula bar.  How is this done by VBA?  The text box is a drawing object on a chart on a separate tab to the worksheet cell I am referencing.  All the text boxes have been named.  

    I have tried something like this but this will only set the textbox equal to the string and not the actual cell reference

    ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text = "=Sheet1!A1"

    Secondly, if I have multiple charts with the same textbox names can I refer to the chart tab name and the textbox name so as to not have to create new textbox names for every instance?  I know the below code is wrong but this essentially what I am trying to do

    Worksheets("Chart1").Shapes("Textbox 1").Formula = Sheet1!A1

    Worksheets("Chart2").Shapes("Textbox 1").Formula = Sheet1!A3

    Tuesday, August 19, 2014 5:39 PM


  • A text box on a chart is an object on that chart rather than on the worksheet. You can use

        Worksheets("Sheet1").ChartObjects("Chart 1").Chart.TextBoxes("TextBox 1").Formula = "=Sheet1!$A$1"

    I prefer to split it into separate commands; this makes it easier to pinpoint problems:

        Dim wsh As Worksheet
        Dim cht As Chart
        Dim tbx As TextBox
        Set wsh = Worksheets("Sheet1")
        Set cht = wsh.ChartObjects("Chart 1").Chart
        Set tbx = cht.TextBoxes("TextBox 1")
        tbx.Formula = "=Sheet1!$A$1"

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by danishani Tuesday, September 16, 2014 5:04 AM
    • Marked as answer by danishani Monday, November 10, 2014 11:31 PM
    Tuesday, August 19, 2014 8:10 PM