A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Charts and Charting in Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Help Programming an XY scatter Chart



 
 
Thread Tools Display Modes
  #1  
Old March 21st 06, 10:15 PM posted to microsoft.public.excel.charting
external usenet poster
 
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

Ads
  #2  
Old March 21st 06, 11:30 PM posted to microsoft.public.excel.charting
external usenet poster
 
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  
Old March 22nd 06, 12:37 PM posted to microsoft.public.excel.charting
external usenet poster
 
Posts: n/a
Default Help Programming an XY scatter Chart

Thanks alot. This really helps.

  #4  
Old March 22nd 06, 02:14 PM posted to microsoft.public.excel.charting
external usenet poster
 
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  
Old March 23rd 06, 02:10 AM posted to microsoft.public.excel.charting
external usenet poster
 
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
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 08:41 PM.


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