lsimps
02-05-2009, 12:27 AM
I am trying to update each chart on a worksheet.
If that chart is based on a pivot table, then do something.
If it is not, skip to a point, do some more testing and maybe update that std chart.
I have 6 pivot charts and 3 std charts on a Sheet "Graphs". The looping works for the 6 pivot charts fine, works OK for the 1st std chart, then errors out on
PivotError = IsError(ActiveChart.PivotLayout.PivotTable.Name)
as it should but the previous command
On Error GoTo SkipErrorHandler
should send it skiperrorhandler
Any ideas?
If i have failed to incl something i apologise as it is my 1st post.
code being used
Sub Update_Graphs()
Dim i As Integer
Dim PivotError As Variant
Sheets("Graphs").Activate
For i = 1 To Sheets("Graphs").ChartObjects.Count
Sheets("Graphs").ChartObjects(i).Activate
On Error GoTo SkipErrorHandler ' if the following assignment fails skip the pivot chart update routine
PivotError = IsError(ActiveChart.PivotLayout.PivotTable.Name)
On Error GoTo 0 ' reset the error handler
If InStr(ActiveChart.PivotLayout.PivotTable.Name, "Appln") > 0 _
Or InStr(ActiveChart.PivotLayout.PivotTable.Name, "Appr") > 0 Then 'it is a pivot chart
With Sheets("Graphs").ChartObjects(i).Chart
.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Dales Chart"
.HasPivotFields = False
.HasTitle = False
If Right(.PivotLayout.PivotFields("Data").PivotItems(1), 1) = "$" Then
.Axes(xlValue).DisplayUnit = xlMillions
Else
.Axes(xlValue).DisplayUnit = xlNone
End If
End With
End If
SkipErrorHandler:
Err.Clear
Next i
End Sub
If that chart is based on a pivot table, then do something.
If it is not, skip to a point, do some more testing and maybe update that std chart.
I have 6 pivot charts and 3 std charts on a Sheet "Graphs". The looping works for the 6 pivot charts fine, works OK for the 1st std chart, then errors out on
PivotError = IsError(ActiveChart.PivotLayout.PivotTable.Name)
as it should but the previous command
On Error GoTo SkipErrorHandler
should send it skiperrorhandler
Any ideas?
If i have failed to incl something i apologise as it is my 1st post.
code being used
Sub Update_Graphs()
Dim i As Integer
Dim PivotError As Variant
Sheets("Graphs").Activate
For i = 1 To Sheets("Graphs").ChartObjects.Count
Sheets("Graphs").ChartObjects(i).Activate
On Error GoTo SkipErrorHandler ' if the following assignment fails skip the pivot chart update routine
PivotError = IsError(ActiveChart.PivotLayout.PivotTable.Name)
On Error GoTo 0 ' reset the error handler
If InStr(ActiveChart.PivotLayout.PivotTable.Name, "Appln") > 0 _
Or InStr(ActiveChart.PivotLayout.PivotTable.Name, "Appr") > 0 Then 'it is a pivot chart
With Sheets("Graphs").ChartObjects(i).Chart
.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Dales Chart"
.HasPivotFields = False
.HasTitle = False
If Right(.PivotLayout.PivotFields("Data").PivotItems(1), 1) = "$" Then
.Axes(xlValue).DisplayUnit = xlMillions
Else
.Axes(xlValue).DisplayUnit = xlNone
End If
End With
End If
SkipErrorHandler:
Err.Clear
Next i
End Sub