Annotating charts with text from within VBA

[John O]

I need to be able to add some text to charts ("Max", "Min", "Target" for
example) from within an Excel VBA Macro. At this point I have been unable to
get this to take place. The code that I had tried is as follows:

' add text box labels
chrtobj.Chart.Shapes.AddLabel(msoTextOrientationHorizontal, 133.5, 105#,
0# _
, 0#).Select
Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue
Selection.Characters.Text = "UPPER"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.Fill.Visible = msoFalse
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 8
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.IncrementLeft -0.36
Selection.ShapeRange.IncrementTop -8.98

But when I use this code (which worked when I recorded it), I get a VBA
"Run-time error '438': Object doesn't support this property or method" and it
takes me to the debugger.

Any ideas on why this is happening and how I can get the labels on the
chart? Thanks,

 

[Jon Peltier]

The code runs without error for me. What line is highlighted in the
debugger? How did you define chrtobj?

- Jon

 

[John O]

I just sent you the section that didn't run. Do you want me to post the
entire subroutine so you can look at it?

Thanks,

 

[Jon Peltier]

No, I asked which line within the code you posted is highlighted in yellow
when the error occurs.

- Jon

 

[John O]

Jon,

Sorry about that. I forgot to answer that question. It is on the second
line in the code fragment that I posted (the first selection statement
"Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue").

 

[Jon Peltier]

I suspect Excel doesn't know what chrtobj is. You need to reference it
somehow:

Dim chrtobj As ChartObject
Set chrtobj = ActiveSheet.ChartObjects(1)

Or maybe use something like this as your command that creates the textbox:

With ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, 133.5,
105#, 0#, 0#)

In fact, using AddLabel ends up with a black background, while AddTextbox
has a white background.

There are a lot of other inefficiencies in the code. This is streamlined a
bit. The AutoFontScale is going to cause problems. The only way I could make
it work is shown in the last line before End Sub.

Sub ChartLabels()
Dim chrtobj As ChartObject

Set chrtobj = ActiveSheet.ChartObjects(1)

' add text box labels
With chrtobj.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 133.5,
105#, 0#, 0#)
With .TextFrame
.AutoSize = msoTrue
.Characters.Text = "UPPER"
With .Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
With .Fill
.Visible = msoFalse
.Solid
.Transparency = 0#
End With
With .Line
.Weight = 0.75
.DashStyle = msoLineSolid
.Style = msoLineSingle
.Transparency = 0#
.Visible = msoTrue
.ForeColor.SchemeColor = 8
.BackColor.RGB = RGB(255, 255, 255) ' TURNS IT BLACK
End With
.IncrementLeft -0.36 ' BUILD THESE INTO INITIAL DIMENSIONS
.IncrementTop -8.98 ' (IN AddLabel STATEMENT ABOVE)
End With
chrtobj.Chart.TextBoxes(chrtobj.Chart.TextBoxes.Count).AutoScaleFont =
False
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[John O]

Jon,

I am not surprised that you found several inefficiencies in the code. I do
not profess to be a good programmer and am just learning about interacting
with charts from VBA. Thank you for the suggested changes. I will try them
out and let you know how it goes.

One thing that I probably should mention is that the code that I posted is
only part of a subroutine that is being called by another macro. The
subroutine as I am using it is called by the following statement:

Sub create_chart(chart_number, XaxisDataArray, YaxisDataArray,
current_worksheet, current_component)

I pass in the X data array (in XaxisDataArray defined as Variant in the
calling subroutine), the Y data array (in YaxisDataArray defined as Variant
in the calling subroutine), the current worksheet where the chart will be
placed (in current_worksheet defined as String in the calling subroutine),
the Y axis label (as current_component defined as Variant in the calling
subroutine), and the chart number (is this the first, second, third, etc.
chart on this worksheet defined as Integer in the calling subroutine and used
to position the charts on the worksheet so they don't overlap).

I create the chart object inside the subroutine by the command

Set chrtobj = ActiveSheet.ChartObjects.Add(10, y_chart_position, 800, 300)

where y_chart_position is defined as an Integer. So, if the subroutine
doesn't know what the chrtobj is, I must be doing something to confuse it in
my code between when I create the chart and when I try to add the text.

If you want to see the entire subroutine as it is, I can send that to you or
post it, whichever is preferred. I must warn you, though, that you will see
plenty more inefficiencies in the code.

Thanks.

 

[John O]

Jon,

The code you sent me works. Thanks again.

Now I just have to figure out how to position the text inside the chart to
where I want it. I want to locate it near one of the lines on the chart. I
know what the data for that line is, but am not sure how to use that
information to position the label. Do you have any suggestions?

 

[Jon Peltier]

My bad. You said the problem was on the second line, and I read it as
"first" line. I had some issues with that line, which is why I played the
silly game to stick the AutoFontScale to the end.

- Jon

 

[Jon Peltier]

You can add the text as the data label for a data point, and it will move as
the point moves. If none of your data goes where you want the label, add a
dummy XY series with a point where the label goes, then hide the dummy
series (no lines, no markers).

- Jon

 

[John O]

Jon,

I am very interested in your suggestion about adding the data label to a
point. I did not realize you could do this. This would be exactly what I
need.

I tried to get the coding for this by recording a macro and going through
the process of adding a data label to a point, but when I paste that code
into my macro that is creating the chart, it fails. The code that was
recorded for me is

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(12).Select
ActiveChart.SeriesCollection(1).Points(12).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShoSeriesName:=True, ShowCategoryName:=False, _
ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = "=""UPPER"""
ActiveChart.SeriesCollection(1).DataLabels.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlContinuous
End With
Selection.Shadow = False
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With

When I paste it into my macro code, I am doing some additional formatting of
the chart, so I pasted it into that section. This is what that code looks
like:

With chartobj.Chart.SeriesCollection(1)
.Border.ColorIndex = 5
.Border.Weight = xlMedium
.Border.LineStyle = xlContinuous
.MarkerBackgroundColorIndex = 5 + i
.MarkerForegroundColorIndex = 5 + i
.MarkerStyle = xlDash
.Smooth = False
.MarkerSize = 2
.Shadow = False
.Name = "UPPER"
With .DataLabels.Border <--- this line gives an error "Unable to set
the ColorIndex property of the Border class"
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With .DataLabels.Interior <--- this line gives an error "Unable to get
the Interior property of the DataLabels class"
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
.Points(1).ApplyDataLabels AutoText:=True, _
LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
End With

If I skip over the 2 With sections that fail, the .Points statement works
and displays the data point label (not boxed or with a white background which
is what the Border and Interior With sections were supposed to do). I admit
that I do not know much about macros, so it may be something quite obvious
that I am doing wrong, but if you (or someone else watching this thread) can
tell me how to correct the error and get my data point label displayed with a
border and a white background, I would really appreciate it.

Thanks,

 

[John O]

Jon,

You can ignore my previous post. I figured out why it was giving me the
error. I should have kept the .points.applydatalabels statement before the 2
with statements. I guess this is because until I apply the data labels,
there is nothing for the border or interior property to contain. I think I
understand a bit better now how some of these properties work.

Thanks for all your help. I really appreciate it.