Dynamic text box labeling

[Paul Lambson]

I am trying to write to a text box in a chart

text box called "text box 1026"
graph is "Chart 26"
on worksheet "graphs"

I can't find how to refernce that text box...

Thanks in advance.

Thanks,
Paul Lambson

This is what I have so far but it's not working

Sheets("GRAPH").Chart("Chart 26").TextBoxes("Text Box 1026") = tday

 

[JLGWhiz]

Your textbox is probably tied to the sheet and not the chart.

 

[Andy Pope]

Hi,

I think the syntax should be more like this,

sheets("GRAPH").chartobjects("Chart 26").chart.textboxes("Text Box
1026").text= TDay

Cheers
Andy

 

[OssieMac]

Hi Paul,

I have the feeling that the previous answers are not what you are after. I
am assuming that the Text Box is created from the Drawing toolbar because I
don't believe that text boxes from either the forms toolbar or control
toolbox can be placed on a chart.

I recorded code in xl2002 to place a value in a drawing text box. (It would
not record in xl2007 but under test the code will work in an xl2007 file). I
have not been able to modify the code to obtain the desired results without
having the code select the text box so below is what I finished up with. You
should be able to modify it for your requirements. I also included a little
extra for inserting source data, the chart title and X and Y axis titles and
I left the recorded code in for formatting the text in the text box in case
that helps also.

I am interested if anyone knows how to modify the code so that it is not
actually necessary to select the text box.


Sub ChartTitles()

Dim tday As String

'Dummy value created for tday for the exercise
tday = Format(Date, "dd mmm yyyy")

Worksheets("Graph").Select

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Shapes("Text Box 2").Select
Selection.Characters.Text = tday
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=16).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

'Some added extra info if required
With Worksheets("Graph") _
.ChartObjects("Chart 1").Chart

'ChartSeriesRange is a named range on the worksheet
.SetSourceData Source:=Sheets("Calc") _
.Range("ChartSeriesRnge"), PlotBy:=xlRows

.HasTitle = True
.ChartTitle.Characters.Text = "My Chart Title"
.ChartTitle.Font.Bold = True

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle. _
Characters.Text = "My X Axis"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle. _
Characters.Text = "My Y Axis"
End With

End Sub

 

[Andy Pope]

Hi OssieMac,

Did you try the code I suggested?
For me it changes the text of a Text box object, from the Drawing toolbar -
actually from the Insert > Text > Textbox in xl2007, without the need to
select it.

Cheers
Andy

 

[Paul Lambson]

Ya I used the code and it worked great. The section I really needed
was;

Sheets("GRAPH").ChartObjects("Chart 26").Activate
ActiveChart.Shapes("Text Box 1026").Select
Selection.Characters.Text = tday

and now the macro works like a charm.

Thanks for the help!!

 

[OssieMac]

Thanks Andy,

I did attempt to use your code but it failed. However, I have tried again
with success and therefore I assume that when I edited to insert my text box
name or something, I possibly introduced an error but I will never know for
certain exactly what I did wrong. Anyway it has answered my question
perfectly.

To Paul,

Andy's answer is certainly the better way to go.

 

[Andy Pope]

Thanks for the info.

Cheers
Andy