Additional lines on the chart

[kzalewski]

Hello,

I have to draw additional lines on the chart from VBA level. I know
the value on the X axis (from data sheet, of course) but I don't know
where this value will be displayed on the chart. Therefore I don't
know where to start drawing. Is there any possibility to access from
VBA to the position of the given value on the X axis?

Regards,

Krzysiek

 

[Jon Peltier]

Add a series using the endpoints of the line to be added, using data you've
calculated in the sheet. Drawing lines (shapes) is likely to cause pain if
the other data changes.

- Jon

 

[kzalewski]

Jon,

Thank you for clue.

I have to draw 2 lines to visualize the range of accepted values from
the X series. Therefore I need to calculate 2 points (x1,y1) as the
beginning of left, minimum line (left top point) and (x2,y2) as the
end of right, maximum line (right bottom point) and then draw the
lines from (x1,y1) to (x1,y2) and from (x2,y1) to (x2,y2). X series
can be displayed from Window_min to Window_max. Values in this series
has step as Divider. Lines must be drawn in position Range_min and
Range_max (values from X series).I resolved this problem as below:

Number_1 = (Window_max - Window_min) / Divider + 1
Number_2 = Chart.Axes(1).Width / Number_1
x1 = Chart.Axes(1).Left + ((Range_min - Window_min) *
Number_2) / Divider
x2 = Chart.Axes(1).Left + ((Range_max - Window_min) *
Number_2) / Divider
y1 = Chart.PlotArea.Top
y2 = Chart.Axes(1).Top
Chart.Shapes.AddLine(x1, y1, x1, y2)
Chart.Shapes.AddLine(x2, y1, x2, y2)

To be harder, I generate the chart form Access based on values from
given table. Because I can have many Yseries, total chart can be not
readable. I'm thinking to add checkboxes for each Yseries to show/hide
activity. And there is next problem: how to add VBA code to
particular checkbox in Excel from VBA code in Access. Do you have any
suggestions?

Regards,

Krzysiek