Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 20
Default How to Draw a chart line connecting 2 points in the middle of the series

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

  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 20
Default How to Draw a chart line connecting 2 points in the middle of the series

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 wrote:
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


  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 20
Default How to Draw a chart line connecting 2 points in the middle of the series

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").Ac tivate
nextseries = ActiveChart.SeriesCollection.Count
'
On Error Resume Next ' if it can't select, then it doesn't exist,
so create.
Charts("ClickChart").SeriesCollection("HiCAGR").Se lect
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 wrote:
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 wrote:
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


  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 20
Default How to Draw a chart line connecting 2 points in the middle of the series

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.


KathyC wrote:
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").Ac tivate
nextseries = ActiveChart.SeriesCollection.Count
'
On Error Resume Next ' if it can't select, then it doesn't exist,
so create.
Charts("ClickChart").SeriesCollection("HiCAGR").Se lect
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 wrote:
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 wrote:
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 radar chart - maximum discrete data points Daniel@UDRI Charts and Charting in Excel 5 August 25th 06 02:57 PM
how can I limit my chart Richard Erlacher Charts and Charting in Excel 7 March 7th 06 01:08 PM
Fan charts Dean Charts and Charting in Excel 7 May 30th 05 11:51 AM
create space in line chart between points, linked to pivot table Mike -Z- Charts and Charting in Excel 1 December 7th 04 10:39 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM


All times are GMT +1. The time now is 08:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"