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

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

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

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

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

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