PDA

View Full Version : Problems with formatting a scatter chart using macros



Jeremy42
06-25-2015, 10:34 AM
I am having problems with formatting a scatter chart using macros. I am able to import all the data into series and change most of the settings,
but I am having problems with part of the code. This code is located on a different sheet then the one the data exists and the chart is being created on hence the awkward coding.



Set myChtObj = Sheets("Output Summary").ChartObjects.Add _
(Left:=380, Width:=551, Top:=296, Height:=405)
With myChtObj
.Name = "CDF Plot"
End With

'Can't get this to work right - doesnt cause error but doesn't actually add the major and minor gridlines
'myChtObj.Chart.ChartArea.Select
'myChtObj.Chart.Axes(xlCategory).Select
'myChtObj.Chart.Axes(xlValue).HasMinorGridlines = True
'myChtObj.Chart.Axes(xlValue).HasMajorGridlines = True

'Can't get this to work right - fails at Worksheets("Output Summary").Chart("CDF Plot").Activate
'Worksheets("Output Summary").Chart("CDF Plot").Activate
'ActiveWorkbook.Sheets("Output Summary").Activate
'ActiveSheet.ChartObjects(1).Activate

'Can't get this to work right - doesnt cause error but doesn't actually add the major and minor gridlines
'Worksheets("Output Summary").ChartObjects(1).Activate
'ActiveChart.ChartArea.Select
'ActiveChart.Axes(xlCategory).Select
'ActiveChart.Axes(xlValue).HasMinorGridlines = True
'ActiveChart.Axes(xlValue).HasMajorGridlines = True

'Can't get this to work right - fails at myChtObj.Chart.Activate
'myChtObj.Chart.Activate
'ActiveChart.ChartArea.Select
'ActiveChart.Axes(xlCategory).Select
'ActiveChart.Axes(xlValue).HasMinorGridlines = True
'ActiveChart.Axes(xlValue).HasMajorGridlines = True

'Can't get this to work right - fails at ThisWorkbook.Worksheets("Output Summary").Charts("CDF Plot").ChartArea.Select
'ThisWorkbook.Worksheets("Output Summary").Charts("CDF Plot").ChartArea.Select
'ThisWorkbook.Worksheets("Output Summary").Charts("CDF Plot").Axes(xlCategory).Select
'ThisWorkbook.Worksheets("Output Summary").Charts("CDF Plot").Axes(xlValue).HasMinorGridlines = True
'ThisWorkbook.Worksheets("Output Summary").Charts("CDF Plot").Axes(xlValue).HasMajorGridlines = True

'Can't get this to work right - fails at ThisWorkbook.Worksheets("Output Summary").ChartObjects(1).ChartArea.Select
'ThisWorkbook.Worksheets("Output Summary").ChartObjects(1).ChartArea.Select
'ThisWorkbook.Worksheets("Output Summary").ChartObjects(1).Axes(xlCategory).Select
'ThisWorkbook.Worksheets("Output Summary").ChartObjects(1).Chart.Axes(xlValue).HasMinorGridlines = True
'ThisWorkbook.Worksheets("Output Summary").ChartObjects(1).Chart.Axes(xlValue).HasMajorGridlines = True

'Can't get this to work right - fails at Sheets("Output Summary").Charts(1).Activate
'Sheets("Output Summary").Charts(1).Activate
'ActiveChart.Axes(xlCategory).Select
'ActiveChart.Axes(xlValue).HasMinorGridlines = True
'ActiveChart.Axes(xlValue).HasMajorGridlines = True

'Cant get this to work right - does not cause error but does not add the axis titles or set the alignment
'myChtObj.Chart.Axes(xlCategory).AxisTitle.Select
'myChtObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Simulation Life (n)"
'myChtObj.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
'myChtObj.Chart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
'myChtObj.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Cumulative Distribution Function (cdf)"


I think its a syntax error, but for the life of me I cannot see it. The chart is in logspace and I need the gridlines to show and have the axis titles shown.
Obviously when I am testing the code I remove the ' out at the first of the line.
The strange thing is that similar code works fine such as:



myChtObj.Chart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0.E+00"
myChtObj.Chart.SetElement (msoElementChartTitleAboveChart)
myChtObj.Chart.ChartTitle.Text = "Cumulative Distribution Function of Simulation Life"

myChtObj.Chart.Axes(xlValue).MaximumScale = 1
myChtObj.Chart.SeriesCollection(1).Select
With Selection
.MarkerStyle = 2
.MarkerSize = 10
.Smooth = True
End With


If anyone could please point out my error and give a detailed explanation as to why that is wrong I would greatly appreciate it (assume I know nothing about coding).

Edit
The same topic is posted at the following forums:
ozgrid.com/forum/showthread.php?t=195452&p=749864#post749864
mrexcel.com/forum/excel-questions/863995-problems-formatting-scatter-chart-using-macros.html#post4195819
excelforum.com/excel-programming-vba-macros/1090349-problems-with-formatting-a-scatter-chart-using-macros.html#post4113229

I had to do it in this format otherwise I get post denied because of URL.