Lesson 40: Drawing vertical line in charts

Sometimes we want to insert vertical lines into our chart to denote certain values in the graph.

An example of a vertical line using the same charts from the last lesson.

Using the same setup from the previous lesson, we want to insert 1 vertical line into the Revenue chart.

We are using the same charts from the previous lesson because this gives you the opportunity to practice doing it on your own with the other chart.

The steps to accomplish the effect as seen above are:

  1. Name the ChartObject you want to insert the vertical line in.
  2. Create a Range of Data that will determine the vertical line.
  3. Create a Subroutine that will “draw” the vertical line and insert it into the correct Chart.
  4. Adjust the Y-axis maximum and minimum range to make the Chart look better.
  5. Call the Subroutine which draws the vertical line AFTER you create the Chart.

Name the ChartObject

This step is especially crucial in our setup because we have two Charts in the same Sheet, so we need a way to know which Chart to insert the vertical line into.

Insert the code labelled Add name to Chart into the DrawRevenueChart subroutine from the last lesson.

Notice that we are giving the name of the ChartObject as Revenue.

Create Range of Data for this Vertical Line

In Sheet 2, we are going to have this set of data.

The Cell B7 in Sheet2 is actually a formula that is 

=MAX(Sheet1!B2:B11)

This is how B7 shows 57.

 The Cell B3 in Sheet2 is actually a formula that is

=B7+10

This is how B3 shows 67.

Create a Subroutine to draw vertical line

Adjust Y-Axis Maximum and Minimum Scale

Call the AddVerticalLine subroutine

Questions:

  1. Why you use Axes(xlValue) to adjust y-axis? Aren’t you really adjusting the x-axis?xlValue means the axis that contains the Values. Usually that is the y-axis. If you want to adjust the x-axis, that would be xlSeries.To learn more about the possible Axes objects, you can look at  xlAxis Enumeration in Excel 2007 Developer Reference.  
  2. I don’t know how you found out about properties such as MaximumScale and MinimumScale. How did you do that?These properties and many other related stuff can be found in Excel 2007 Developer Reference. This particular property (MaximumScale) is found here. Chart.Axes is another useful property I used. It is found here.

Last updated by at .

<< Previous | Next >>