Constant horizontal line on a chart (based on a single value)

Let's assume that your desired Y-intercept value
is in cell Sheet1!$A$1. First, create a defined
name called Sheet1!Y_Intercept_Value with a
reference of ={1,1}*Sheet1!$A$1. Select your
chart area and on the formula bar enter the formula,
=SERIES(,,Sheet1!Y_Intercept_Value,1). Y=A1 should
now be plotted on your chart!

Hi SQR,

I would suggest that you do use a Shape (line) object. Just set up a macro that draws the line the width of the plot area (Width property) and aligned with the left side of the plot area (Left property), and use its Top property to align it with the value on the graph's ordinate axis that you want the line to represent. Then call this routine whenever the chart is calculated or resized (chart Calculate and Resize events) because this might cause the axes to change. You could also assign the line itself to the macro so that it would re-calculate its position whenever you click on the line. This way, you could easily update it whenever you want, such as when you drag the chart to a new position, resize it, or modify it in any way that might throw the line out of alignment.

I hope this helps. If you need an example, let me know.

Damon

Thanks, but I could do with a bit more help...

This is exactly what I need to do, I think, but
how exactly do I create a defined name (and I
assume I can make 2 for max/min values if I need
a Y axis range marked out).

Just in case it's easy, can the area between two
such lines be shaded? Ignore this if you like, it's
in no way essential...

Thanks again,
SQR

Erm, I need an example! This sounds useful since
it looks like it would offer other opportunities
such as shaded areas but would the line (and more
importantly any shading) appear superimposed over
the chart's lines or could it be set to be behind
the chart details... or is that getting too weird?

If you can show me a simple sample macro for
plotting a line as you described, fixed in
dimensions according to the chart area and placed
at a point whose value is in a cell on a separate
sheet, that would be great as I have no clue which
way (macro drawing object vs. chart thing) will be
best and/or easier to remember!

Thanks a lot,
SQR

Let's tackle this one issue at a time...

> how exactly do I create a defined name

Choose the Insert | Name | Define... menu
command. Enter Sheet1!Y_Intercept_Value in
the "Names in workbook field"; enter
={1,1}*Sheet1!$A$1 in the "Refers to" field;
and press [ OK ].

> I assume I can make 2

Sure. Name'em Sheet1!Y_Intercept_Value1 and
Sheet1!Y_Intercept_Value2 if you like.

> can the area between two such lines be shaded

Yes; you just use an Area chart type instead of
a line, and make the fill color of the lesser
value the same as the plot area background color.

Re: Constant horizontal line on a chart, shading

SQR,

Yes, either lines or shaded areas could underlie the chart (you would have to turn off the chart area fill, and cells and cell contents would then show thru if not empty or filled white). Either lines or shaded areas could also lie on top of the chart with a semi-transparent fill for areas that would allow the chart to show through. I would be happy to send you an example of each of these if you email me so I have your email address. Make sure to remind me what this is about as I get a lot of email questions.

mailto:VBAexpert@piadamon.com

Damon

Thanks for everything! (no message, don't click)

.