link value of cell in last column to inserted text box (via vba)

[suremac]

Greetings,

I'm trying to create a dynamic chart title by inserting a text box in the chart title that displays the value of the last populated cell in column A. The number of rows increases over time, so I'm trying to come up with a dynamic cell reference. When I hover over the text box a tool tip appears with this text "TextBox 2" so I assume that is the name of the text box. I'm definitely open to other methods that do not use vba. It seems that none of this code can activate the text box:

Sub textbox()
    Worksheets("Figure3-5").TextBoxes("TextBox 2").Range("A" & Rows.Count).End(xlUp).Value
    End Sub

Sub textbox()
    Worksheets("Figure3-5").TextBox2.Value = Range("A" & Rows.Count).End(xlUp).Value
    End Sub

Sub textbox()
    ActiveSheet.Shapes("TextBox 2").Select
    Selection.Characters.Text = Range("A" & Rows.Count).End(xlUp).Value
    End Sub

Thanks in advance for any help.

Regards,

 

[suremac]

Through a lot of trial and error I got this code to work:

Worksheets("Figure3-5").ChartObjects("Chart1").Activate
ActiveChart.Shapes("TextBox 2").TextFrame.Characters.Text = Range("A" & Rows.Count).End(xlUp).Value