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.
--
John O


"John O" wrote:

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).ApplyDa taLabels 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 Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"John O" wrote in message
...
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?

--
John O


"Jon Peltier" wrote:

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(msoTextOrientationHori zontal, 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(msoTextOrientation Horizontal,
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.Co unt).AutoScaleFont =
False
End Sub


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



"John O" wrote in message
...
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").

--
John O


"Jon Peltier" wrote:

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

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


"John O" wrote in message
...
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,
--
John O


"Jon Peltier" wrote:

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

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


"John O" wrote in message
...
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(msoTextOrientationHo rizontal,
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,

--
John O