Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 21st 06, 10:15 PM posted to microsoft.public.excel.charting
aj
 
Posts: n/a
Default Help Programming an XY scatter Chart

I have a user form that inputs data onto a spreadsheet. I have a
command button that runs a macro that activates the sheet, selects the
data, and creates the XY Scatter chart. My Problem is that I don't know
how to set a loop to set each series' properties . (The number of
series changes every time) Bellow is an example where I manually set
the properties for three of the series. Any Ideas on how to create a
loop to set all.

Range("A:A,C:C,D").Select
Range("D1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1:A15,C115"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C4"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C3"
ActiveChart.SeriesCollection(1).name = "=Sheet1!R2C1"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R3C4"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R3C3"
ActiveChart.SeriesCollection(2).name = "=Sheet1!R3C1"
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!R4C4"
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R4C3"
ActiveChart.SeriesCollection(3).name = "=Sheet1!R4C1"
ActiveChart.Location Whe=xlLocationAsNewSheet, name:="xyCart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "risk"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Impact"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Probability"
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
End Sub


  #2   Report Post  
Old March 21st 06, 11:30 PM posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default Help Programming an XY scatter Chart

This procedure does what you asked. Note that there are still a few
redundant and unneeded steps (the chart recorder records even default
settings).

Sub DoTheChart()
Dim WS As Worksheet
Dim Cht As Chart
Dim Rng As Range
Dim iRow As Long

Set WS = ActiveSheet
Set Cht = Charts.Add
Cht.ChartType = xlXYScatter

Set Rng = WS.Range(WS.Range("A2"), WS.Range("A2").End(xlDown))
For iRow = 2 To 1 + Rng.Rows.Count
Cht.SeriesCollection.NewSeries
With Cht.SeriesCollection(iRow - 1)
.XValues = "='" & WS.Name & "'!R" & iRow & "C4"
.Values = "='" & WS.Name & "'!R" & iRow & "C3"
.Name = "='" & WS.Name & "'!R" & iRow & "C1"
End With
Next
With Cht
.HasTitle = True
.ChartTitle.Text = "risk"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Impact"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Probability"
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
End With
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With Cht.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
Cht.HasLegend = False
End Sub

See this page for more about charts and VBA:

http://peltiertech.com/Excel/ChartsH...kChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______


"aj" wrote in message
oups.com...
I have a user form that inputs data onto a spreadsheet. I have a
command button that runs a macro that activates the sheet, selects the
data, and creates the XY Scatter chart. My Problem is that I don't know
how to set a loop to set each series' properties . (The number of
series changes every time) Bellow is an example where I manually set
the properties for three of the series. Any Ideas on how to create a
loop to set all.

Range("A:A,C:C,D").Select
Range("D1").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A1:A15,C115"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C4"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C3"
ActiveChart.SeriesCollection(1).name = "=Sheet1!R2C1"
ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R3C4"
ActiveChart.SeriesCollection(2).Values = "=Sheet1!R3C3"
ActiveChart.SeriesCollection(2).name = "=Sheet1!R3C1"
ActiveChart.SeriesCollection(3).XValues = "=Sheet1!R4C4"
ActiveChart.SeriesCollection(3).Values = "=Sheet1!R4C3"
ActiveChart.SeriesCollection(3).name = "=Sheet1!R4C1"
ActiveChart.Location Whe=xlLocationAsNewSheet, name:="xyCart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "risk"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Impact"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Probability"
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
End Sub



  #3   Report Post  
Old March 22nd 06, 12:37 PM posted to microsoft.public.excel.charting
aj
 
Posts: n/a
Default Help Programming an XY scatter Chart

Thanks alot. This really helps.

  #4   Report Post  
Old March 22nd 06, 02:14 PM posted to microsoft.public.excel.charting
aj
 
Posts: n/a
Default Help Programming an XY scatter Chart

Thanks again. The stuff above really helped. I am having 2 more issues.
1. How do i set the chart location to create a new sheet called
"RiskMatrix" the first time the create chart button is pressed and then
have it replace the old "RiskMatrix" chart everytime it is pressed
again. 2. I want to format the gridlines but I get an error claiming it
can't change the MinimumScale.

Here is what I added to your code
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
' This is what I addedd
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

  #5   Report Post  
Old March 23rd 06, 02:10 AM posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default Help Programming an XY scatter Chart

1. This code should work:

Sub DoTheChart()
Dim WS As Worksheet
Dim Cht As Chart
Dim Rng As Range
Dim iRow As Long

' ignore error if "RiskMatrix" doesn't exist
On Error Resume Next
ActiveWorkbook.Charts("RiskMatrix").Delete
On Error Goto 0

Set WS = ActiveSheet
Set Cht = Charts.Add
Cht.Name = "RiskMatrix"
Cht.ChartType = xlXYScatter

Set Rng = WS.Range(WS.Range("A2"), WS.Range("A2").End(xlDown))
For iRow = 2 To 1 + Rng.Rows.Count
Cht.SeriesCollection.NewSeries
With Cht.SeriesCollection(iRow - 1)
.XValues = "='" & WS.Name & "'!R" & iRow & "C4"
.Values = "='" & WS.Name & "'!R" & iRow & "C3"
.Name = "='" & WS.Name & "'!R" & iRow & "C1"
End With
Next
With Cht
.HasTitle = True
.ChartTitle.Text = "risk"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Impact"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Text = "Probability"
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = False
End With
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With Cht.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
Cht.HasLegend = False
End Sub

2. You can't adjust the gridline parameters. The gridline uses the axis
parameters, so set these instead, and the gridlines will adjust accordingly.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"aj" wrote in message
ups.com...
Thanks again. The stuff above really helped. I am having 2 more issues.
1. How do i set the chart location to create a new sheet called
"RiskMatrix" the first time the create chart button is pressed and then
have it replace the old "RiskMatrix" chart everytime it is pressed
again. 2. I want to format the gridlines but I get an error claiming it
can't change the MinimumScale.

Here is what I added to your code
With Cht.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
' This is what I addedd
.MinimumScale = 0
.MaximumScale = 5
.MinorUnitIsAuto = True
.MajorUnit = 1.667
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With





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
Help Programming an XY scatter Chart aj Charts and Charting in Excel 0 March 21st 06 10:15 PM
date in scatter chart excel 2003 dboudry Charts and Charting in Excel 1 March 16th 06 04:21 PM
Combination stock and scatter chart ArtZ Charts and Charting in Excel 1 January 8th 06 07:59 PM
Line chart or scatter chart?? rmellison Excel Discussion (Misc queries) 0 September 9th 05 10:31 AM
Line chart or scatter chart?? rmellison Excel Discussion (Misc queries) 2 September 7th 05 12:04 PM


All times are GMT +1. The time now is 03:49 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017