Linking textbox to cell

[deftones]

I have several textboxes on a chart that I want linked to different cells on another worksheet. I know (not using vba) that I can select the text box, then in the formula bar type "=Sheet1!K7" and the text box will link. What is the equivolant of doing this in vba?! I've searched everywhere. If I had a userform, I could do something like this

TextBox1.ControlSource = "Sheet2!A1"

but this doesn't seem to work not in a userform. Any ideas?! I'm desperate.
</pre>

 

[Damon Ostrander]

Hi deftones,

I don't believe there is a way to link a Chart textbox's text to a cell via VBA as you can manually. However, there is a simple workaround using the worksheet's Change event. Here's an example:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$1" Then
      Sheet1.ChartObjects("Chart 2").Chart.Shapes("TextBox 1").TextFrame.Characters.Text = Target.Text
   End If
End Sub

This example sets the text in Textbox 1 to whatever text is entered in A1. This Sub must be placed in the worksheet's Code module (the worksheet containing the cell you want to link the textbox to). Note the reference to Sheet1. This is the sheet where the chart resides.

I should mention that you can link textboxes that are a standard part of a chart the way you originally described. For example, the chart title and axis label textboxes. Here's an example of doing this for an axis label:

Sheet1.ChartObjects("Chart 2").Chart.Axes(xlValue).AxisTitle.Caption = "=Sheet2!C1"

I hope you find this helpful.

Keep Excelling.

Damon

 

[deftones]

Thanks for the reply Damon. After a looooot of googling, I finally found the answer. It's as simple as:

ActiveSheet.TextBox(1).Formula = "Sheet1!A1"

 

[Damon Ostrander]

Hi deftones,

You're right. And your solution even works for textboxes on an embedded chart (which is what I thought you were referring to, but apparently was mistaken). If the textbox is on an embedded chart the code would look like this:

Sheet1.ChartObjects("Chart 2").Chart.TextBoxes("TextBox 1").Formula = "Sheet2!$b$1"

Keep Excelling.

Damon