Help with excel 2007 charts macros

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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




1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
if you could post a sample book with chart, one may be able to see why
See if top and left for chart are being properly set