How to Draw a chart line connecting 2 points in the middle of the series

[KathyC]

Hi........I'm fairly new to VBA coding, so forgive me if I'm missing
something very basic!

I've created an embedded chart on my sheet called "ClickChart" that's
using data on another worksheet (that has other embedded charts on it).

The ClickChart's Logarithmic Line chart plots a stock's price over the
user-inputted date range. My goal is to plot user-inputted High, Low,
and Average GrowthRate lines for that stock. So far, using Jon
Peletier's instructions (thanks!),I have captured the endpoint info
(date, price) with the MouseUp event and placed it on the worksheet,
but now I'm not sure how to draw the line between the endpoints.

I've tried adding another series with just the 2 points, but the line
is not plotted anywhere near correctly. When I change that series' x
values to just the 2 dates, then the Price lines goes away entirely.

I'd love to know how the heck to do this! If I haven't given you
enough info, let me know.

Thanks much,

KathyC

 

[KathyC]

Let me add that besides connecting the points, I'd like to be able to
extend the lines out into the future and then get the Yvalues of
certain points: as in, "if the stock contines at this growth rate, it
will be priced at $40 2 years from now, $55 3 years from now, etc".

I've tried adding a trendline, but that doesn't connect the points.
I've also used the x and y values that come from Mouseup to draw a
shape, but those values did not plot the line where I expected.... and
I'm not sure if I could get future values from a shape... can I?

Thanks for any help. I've been searching around on this board and
others, but haven't found an answer yet.

Kathy
PS: Sorry for misspelling Jon's last name in the previous post!

 

[KathyC]

HI..

I worked on this all day yesterday, but still haven't gotten a
solution. I've been through many gyrations and my code now doesn't
resemble that of the initial problem. This is what I've got now.
Stepping through, it goes through the AddSeries sub, and doesn't give
any error message, but the series is not created. To begin I only have
series 1 = Price. After running the code, there's still only one
series. (Users will be able to plot up to 5 series and I don't know
which order the will be created in, so I need to be able to name the
series right away so that I can reference the name rather than number
in the rest of the code.)

Any help will be greatly appreciated!
Kathy

Dim PriceRange As Range
Dim DateRange As Range
Dim nextseries As Integer
Dim Seriesname As String

Set PriceRange = Sheets("ClickChart").Range("H6:I6")
Set DateRange = Sheets("ClickChart").Range("H5:I5")
'
Sheets("ClickChart").ChartObjects("ClickChart").Activate
nextseries = ActiveChart.SeriesCollection.Count
'
On Error Resume Next ' if it can't select, then it doesn't exist,
so create.
Charts("ClickChart").SeriesCollection("HiCAGR").Select
If Err <> 0 Then
nextseries = nextseries + 1
Seriesname = "HiCAGR"
Call AddSeries(Seriesname)
End If


' ActiveChart.PlotArea.Select ' needed to have line display
With ActiveChart.SeriesCollection("HiCAGR")
.XValues = DateRange ' dates
.Values = PriceRange ' price
.Border.ColorIndex = 4 ' bright green
.Border.Weight = xlThin
.Border.LineStyle = xlContinuous
.MarkerStyle = xlNone
.Smooth = True
.Shadow = False
End With
End Sub
'
'
Sub AddSeries(Seriesname)
Charts("ClickChart").SeriesCollection.Add _
Source:=Worksheets("ClickChart").Range("H5:I5")

With ActiveChart.SeriesCollection(nextseries)
.Name = Seriesname
.ChartType = xlXYScatter
.AxisGroup = 2
End With

End Sub

 

[KathyC]

Finally found my trouble....

I forgot to move my Dim statements to the top of the sheet and
therefore they only applied to the sub they were in.... very
embarassing.

Well, I guess I only wasted my own time, since nobody else seems to be
around.