craigwg
11-01-2010, 08:33 AM
I have three modules that are straight forward. The first one deletes the contents of the sheet, the second creates a pivot table and chart and the third creates a second pivot table and chart.
I fire these modules in the order upon Worksheet_Activate. All the modules run great if I fire them one at a time individually, and in my testing about 1/20 times they work just fine when the worksheet is activated. But most the time Excel Crashes.
What is the problem? Obviously having two pivot_tables and charts on the same worksheet is fine. Is there a problem with the way I am deleting the contents of the sheet? Another theory I have is that Excel is running the modules too fast and they are bumping into each other? But that makes no sense, especially considering that they run just fine when I fire them manually.
Below is my code in the order that it is firing on Worksheet_Activate. Thanks for your help!
-------------------------------------
Sub DeleteCharts()
Cells.Delete
End Sub
-------------------------------------
Sub CreateChartOne()
ActiveWorkbook.Worksheets("Day by Day Pivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Charts!R30C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Charts").Select
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Top = 10 ' reposition
.Left = 10 ' reposition
.Height = 310 ' resize
.Width = 350 ' resize
End With
With ActiveChart
.SetSourceData Source:=Range("'Charts'!$A$30:$C$47")
.ChartType = xlLine
.ApplyLayout (3)
.ChartTitle.Text = "Calories, Carbohydrates and Fiber"
End With
ActiveWorkbook.ShowPivotChartActiveFields = False
ActiveWorkbook.ShowPivotTableFieldList = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Meal")
.Orientation = xlPageField
.Position = 2
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Calories"), "Count of Calories", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Calories")
.Caption = "Sum of Calories"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(1).Smooth = True
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Carbohydrates"), "Count of Carbohydrates", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Carbohydrates")
.Caption = "Sum of Carbohydrates"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(2).Smooth = True
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Dietary Fiber"), "Count of Dietary Fiber", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Dietary Fiber")
.Caption = "Sum of Dietary Fiber"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(3).Smooth = True
ActiveChart.SeriesCollection(3).AxisGroup = 2
End With
End Sub
-------------------------------------
Sub CreateChartTwo()
ActiveWorkbook.Worksheets("Day by Day Pivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Charts!R26C9", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion12
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Top = 10 ' reposition
.Left = 555 ' reposition
.Height = 310 ' resize
.Width = 350 ' resize
End With
ActiveChart.ApplyLayout (3)
ActiveChart.ChartTitle.Text = "Calories, Carbohydrates and Fiber"
ActiveChart.SetSourceData Source:=Range("'Charts'!$I$26:$K$43")
ActiveWorkbook.ShowPivotChartActiveFields = False
ActiveChart.ChartType = xlCylinderColStacked
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Meal")
.Orientation = xlColumnField
.Position = 1
.PivotItems("(blank)").Visible = False
.PivotItems("Dinner").Position = 3
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Calories"), "Count of Calories", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Calories")
.Caption = "Sum of Calories"
.Function = xlSum
.NumberFormat = "0.0"
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect "Date[All]", xlLabelOnly + _
xlFirstRow, True
'Range("K27:L27").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "Dinner", xlDataAndLabel, _
True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Up:=12
End Sub
I fire these modules in the order upon Worksheet_Activate. All the modules run great if I fire them one at a time individually, and in my testing about 1/20 times they work just fine when the worksheet is activated. But most the time Excel Crashes.
What is the problem? Obviously having two pivot_tables and charts on the same worksheet is fine. Is there a problem with the way I am deleting the contents of the sheet? Another theory I have is that Excel is running the modules too fast and they are bumping into each other? But that makes no sense, especially considering that they run just fine when I fire them manually.
Below is my code in the order that it is firing on Worksheet_Activate. Thanks for your help!
-------------------------------------
Sub DeleteCharts()
Cells.Delete
End Sub
-------------------------------------
Sub CreateChartOne()
ActiveWorkbook.Worksheets("Day by Day Pivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Charts!R30C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion12
Sheets("Charts").Select
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Top = 10 ' reposition
.Left = 10 ' reposition
.Height = 310 ' resize
.Width = 350 ' resize
End With
With ActiveChart
.SetSourceData Source:=Range("'Charts'!$A$30:$C$47")
.ChartType = xlLine
.ApplyLayout (3)
.ChartTitle.Text = "Calories, Carbohydrates and Fiber"
End With
ActiveWorkbook.ShowPivotChartActiveFields = False
ActiveWorkbook.ShowPivotTableFieldList = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Meal")
.Orientation = xlPageField
.Position = 2
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Calories"), "Count of Calories", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Calories")
.Caption = "Sum of Calories"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(1).Smooth = True
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Carbohydrates"), "Count of Carbohydrates", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Carbohydrates")
.Caption = "Sum of Carbohydrates"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(2).Smooth = True
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Dietary Fiber"), "Count of Dietary Fiber", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Count of Dietary Fiber")
.Caption = "Sum of Dietary Fiber"
.Function = xlSum
.NumberFormat = "0.0"
ActiveChart.SeriesCollection(3).Smooth = True
ActiveChart.SeriesCollection(3).AxisGroup = 2
End With
End Sub
-------------------------------------
Sub CreateChartTwo()
ActiveWorkbook.Worksheets("Day by Day Pivot").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Charts!R26C9", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion12
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Top = 10 ' reposition
.Left = 555 ' reposition
.Height = 310 ' resize
.Width = 350 ' resize
End With
ActiveChart.ApplyLayout (3)
ActiveChart.ChartTitle.Text = "Calories, Carbohydrates and Fiber"
ActiveChart.SetSourceData Source:=Range("'Charts'!$I$26:$K$43")
ActiveWorkbook.ShowPivotChartActiveFields = False
ActiveChart.ChartType = xlCylinderColStacked
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Meal")
.Orientation = xlColumnField
.Position = 1
.PivotItems("(blank)").Visible = False
.PivotItems("Dinner").Position = 3
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Calories"), "Count of Calories", xlCount
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Calories")
.Caption = "Sum of Calories"
.Function = xlSum
.NumberFormat = "0.0"
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect "Date[All]", xlLabelOnly + _
xlFirstRow, True
'Range("K27:L27").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "Dinner", xlDataAndLabel, _
True
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Up:=12
End Sub