Hello,
I have a macro that creates 3 charts. One is a simple bar chart and the other is actually two I've combined so that I have a bar and line graph plotted together. My problem is that the placement of the charts fluctuates each time I run the macro. Sometimes the alignment is just right and at other times it seems a location artifact leaves residual information in the macro and has them all over the page.
I'm new at this and have been looking for ways to clear or reset the chart location. Any help would be much appreciated. Code is below:
Sub NBImac()
' Macro1 Macro
' CHART 1
'
'MSChart1.ToDefaults
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'NBI'!$A$3:$C$4")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.PlotBy = xlRows
'ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 3.3
ActiveChart.Axes(xlValue).MinimumScale = 1
ActiveChart.Axes(xlValue).MaximumScale = 4.3
ActiveChart.Axes(xlValue).MaximumScale = 5
ActiveChart.Axes(xlValue).MajorUnit = 0.1
ActiveChart.Axes(xlValue).MajorUnit = 0.5
ActiveChart.Axes(xlValue).Select
ActiveChart.SeriesCollection(1).Name = "=""Newark Beth Israel-Psychiatry Mean"""
ActiveChart.SeriesCollection(2).Name = "=""NYCOM Class-Psychiatry Mean"""
ActiveChart.SeriesCollection(2).XValues = "={2009,2010,2011}"
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Interior.ColorIndex = 41
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Interior.ColorIndex = 1
ActiveChart.ChartTitle.Text = "Newark Beth Israel Recommend This Rotation"
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:="NBIChart"
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).IncrementLeft -150
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).IncrementTop -50
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Height = 250
' CHART 2
'
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'NBI'!$A$7:$C$11")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.PlotBy = xlRows
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MinimumScale = 1
ActiveChart.Axes(xlValue).MaximumScale = 5
Selection.TickLabels.NumberFormat = "@"
ActiveChart.SeriesCollection(1).Name = "=""meaningfully engaged"""
ActiveChart.SeriesCollection(2).Name = "=""Physicians committed to teaching"""
ActiveChart.SeriesCollection(3).Name = "=""DME Responsive"""
ActiveChart.SeriesCollection(4).Name = "=""Adequate supervision and feedback"""
ActiveChart.SeriesCollection(5).Name = _
"=""Perform procedures relevent to level of training"""
ActiveChart.SeriesCollection(5).XValues = "={2009,2010,2011}"
ActiveChart.Legend.Select
Selection.Height = 151.994
Selection.Height = 189.994
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "Newark Beth Israel (color bars)"
Range("F10").Select
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).IncrementLeft -150
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).IncrementTop 239
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Height = 275
ActiveSheet.ChartObjects(2).Chart.PlotArea.Height = 200
ActiveSheet.ChartObjects(2).Activate
ActiveChart.PlotArea.Select
With Selection
.Top = 150
.Height = 200
End With
ActiveSheet.ChartObjects(2).Activate
ActiveChart.Legend.Select
With Selection
.Top = 170
.Height = 212
End With
' CHART 3 - Linegraph
'
Sheets("NBI").Select
Range("A14:A30").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartStyle = 1
ActiveChart.SetSourceData Source:=Range("'NBI'!$A$14:$A$30")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 3.6
ActiveChart.Axes(xlValue).MinimumScale = 1
ActiveChart.Axes(xlValue).MaximumScale = 4.3
ActiveChart.Axes(xlValue).MaximumScale = 5
ActiveChart.Axes(xlValue).MajorUnit = 0.1
ActiveChart.Axes(xlValue).MajorUnit = 0.5
ActiveChart.Legend.Select
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.SeriesCollection(1).Name = "=""NYCOM class mean """
ActiveChart.Location Where:=xlLocationAsObject, Name:="NBIChart"
ActiveChart.ChartArea.Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground2
.ForeColor.TintAndShade = 1
.Transparency = 1
End With
ActiveChart.PlotArea.Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground2
.ForeColor.TintAndShade = 1
.Transparency = 1
End With
ActiveChart.ChartTitle.Select
Selection.Delete
ActiveChart.Axes(xlCategory).Select
Selection.Delete
ActiveSheet.ChartObjects.Select
Selection.Width = 190
Selection.Height = 209
Selection.Left = 72
Selection.Top = 400
ActiveChart.SetElement (msoElementPrimaryValueAxisNone)
ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)
ActiveChart.ChartArea.Border.LineStyle = xlNone
'
End Sub
See more
