Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Help setting the gridlines on a XY Scatter chart with vba
I want to format the gridlines but I get an error claiming it
can't change the MinimumScale. Here is the format I would like to use for the gridlines .MinimumScale = 0 .MaximumScale = 5 .MinorUnitIsAuto = True .MajorUnit = 1.667 .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone Here is my code Private Sub CommandButton1_Click() Sheets("Report").Activate 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" .ApplyDataLabels AutoText:=True, LegendKey:= _ False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, _ ShowPercentage:=False, ShowBubbleSize:=False 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 .HasLegend = False End With With Cht.Axes(xlCategory) .HasMajorGridlines = True .HasMinorGridlines = False ' It worked unilt I added This .MinimumScale = 0 .MaximumScale = 5 .MinorUnitIsAuto = True .MajorUnit = 1.667 .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With With Cht.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False ' It worked unilt I added This .MinimumScale = 0 .MaximumScale = 5 .MinorUnitIsAuto = True .MajorUnit = 1.667 .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With End With End Sub |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.charting
|
|||
|
|||
Help setting the gridlines on a XY Scatter chart with vba
The gridlines use the same scaling parameters as the primary axis. Change
the settings for the axis, and the gridline 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 oups.com... I want to format the gridlines but I get an error claiming it can't change the MinimumScale. Here is the format I would like to use for the gridlines .MinimumScale = 0 .MaximumScale = 5 .MinorUnitIsAuto = True .MajorUnit = 1.667 .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone Here is my code Private Sub CommandButton1_Click() Sheets("Report").Activate 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" .ApplyDataLabels AutoText:=True, LegendKey:= _ False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, _ ShowPercentage:=False, ShowBubbleSize:=False 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 .HasLegend = False End With With Cht.Axes(xlCategory) .HasMajorGridlines = True .HasMinorGridlines = False ' It worked unilt I added This .MinimumScale = 0 .MaximumScale = 5 .MinorUnitIsAuto = True .MajorUnit = 1.667 .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With With Cht.Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False ' It worked unilt I added This .MinimumScale = 0 .MaximumScale = 5 .MinorUnitIsAuto = True .MajorUnit = 1.667 .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date in scatter chart excel 2003 | Charts and Charting in Excel | |||
Combination stock and scatter chart | Charts and Charting in Excel | |||
Line chart or scatter chart?? | Excel Discussion (Misc queries) | |||
Line chart or scatter chart?? | Excel Discussion (Misc queries) | |||
Scatter Chart Axis as text in C# Web appl | Charts and Charting in Excel |