Draw lines in a chart

I would like to connect two data markers with a line to form a rectangular box. Can this be done? Can this process be automated using Macros? Thanks.

You could use formulas (or VBA) to create the five pairs of points for an additional series that forms a box.

Hi SHG - Thank you for your response.

I also recorded a macro by invoking Picture|AutoShapes from the menu and then drew the lines to form a rectangular box. However, the Macro does not adapt to newer data markers after user data has been changed. I would like the rectangular box to be drawn properly even when the data changes.

Thanks again SHG. I don't understand what the x1 y1 etc. is. But here is what I am facing.

1. The graph produces a trend line
2. There are two sets of data marker of which one reflects new data
3. Starting at the lower most marker, a line needs to be drawn to the left, to the point where this line intercepts the trend line
4. Starting at the upper data marker, a line is to be drawn to the left
5. Then the rectangle is completed by drawing vertical lines.

This process has to be repeated for as many sets of markers that are available on the chart. I hope this is not too very confusing. Thanks.

Each data marker has an associated pair of coordinates (x, y), right? If we're talking about two data markers, then we could refer to two points, say (x1, y1) and (x2, y2).

From those, you can create five points using formulas that reference the components of those two ...

x1 y1 ' the first point
x2 y2 ' the second point

=x1 =y1 ' formulas that refer to the above
=x2 =y1
=x2 =y2
=x1 =y2
=x1 =y1

... and you can plot the new points as a series, which will appear as a rectangle that intersects the two data markers at opposite corners.

SHG - thank you for your response. I need a bit more guidance here as I hav e never worked with the material you are suggesting. Thanks.

Hi Andy. Thank you for your response. Here is what I am attempting to accomplish.

There are three sets of data:

1. Policy Line
2. Minimum
3. Maximum

1, Policy Linea. X axis is Point 100 – 1000b. Y axis is Dollar Amount ($0 – $80,000)c. At 100 Points, the trend line starts at $28,107d. At 1000 Points, the trend line ends at $78,070

2. Minimum
a. Has 4 levels
b. At 200 Points the Minimum is $23,000; at 400 Points, Minimum is
$33,000; at 650 Points, Minimum is $48,000; at 1000 Points,
Minimum is $61,000

4. Maximum
a. Has 4 levels too
b. Maximum for each of the Points identified in (2) above are, $33,000,
$50,000, $60,000, and $80,000 respectively.

Objective now is to:

1. Start at the lower left side and draw left from the first minimum point until you reach a point where you would intersect a vertical line coming up from the 100 point mark.

2. Start at the lower left side and draw left from the first maximum point until you reach a point where you would intersect a vertical line coming up from the 100 point mark.

3. The draw vertical lines to connect the two horizontal lines.

4. Repeat this process for each level (in this case 4, but could be more).

Thanks again.

It really would be easier if you posted a workbook. And perhaps include boxes you've drawn yourself to illustrate what you want.

Hi SHG:

Here is the worksheet. I have currently drawn the lines in manually using Autoshapes. Thanks.

Hi SHG:

The lines in the worksheet that has been posted here should start at each of the data markers (Blue and Pink)to form rectangles for each of the four levels, contrary to how it is displayed in the uploaded worksheet. Thanks.

The workbook you posted does not have the data that's plotted -- it's linked to some external file. Please try again.

Hi Andy:

Thank you for your kind response.

The line should connect the minimum and maximum points vertically and move left to the respective minimum point value, thereby forming a rectangle. This process should repeat for each pay grade. Thanks.

Hi SHG:

Here is the worksheet again. Thanks.

As near as I can figure out what you're trying to do, please find attached.

Hi SHG:

Thank you for your kind response. Your approach works well for me. I now have macros doing the boxes.

Andy - thank you for your assistance too.

SHG:

Is there a way of suppressing the word "Series 4" etc from being displayed on the chart? Thanks.

As far as I know, if you show a legend, all series will have a name. You could delete the legend add add your own as a text box.

Instructions on removing legend entry.

My Excel lesson for the day, thanks.

SHG and Andy:

Thanks much. As iI experimented with deleting the legend, I accidentally clicked the entire legend box and deleted it. and noticed that running the code again did not re-create the legend part of the chart at all. Is this typical? Thanks.

We haven't see your code.

To restore the legend, select the chart, and do Chart > Chart Options, Legend, ...

The chart in question is populated each time a command button on the worksheet is clicked. However, each time the button is clicked, a new set of series to draw the rectangles appears in the legend on the chart.

How can I avoid this? Thanks.

Hi Andy:

Thank you for your response.

I have recorded macros that plot the four points and produces the rectangle. These in turn add a new set of series each time the macro is run.

One option is that I can record another macro that removes the word "series" from the legend where applicable. The problem is that, if I record a macro to remove say the four "series" that are currently on the chart -the macro will in the future remove only four. What happens if there are more than four.

More specifically, when the source data is invoked on the chart, there is both an Add and Remove button. How would I approach remove the unwanted one right there using VBA code? Thanks.

Hi Andy:

What are code tags? Thanks.

Hi Andy:

Where in the code posted should the code you provided go. I am not sure where to place this code. Thanks.