PDA

View Full Version : Solved: On Error Goto Failing



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

Jan Karel Pieterse
02-05-2009, 01:57 AM
you must use one of the resume statements within an error handler.

Modify your code like this:
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

lsimps
02-05-2009, 03:40 PM
Thanks Jan

Cutdown final solution below.

Jan, do you know of another way i can test a chart to see if its data is sourced from a pivot table?


Sub Update_Graphs()

On Error GoTo SkipErrorHandler
For i = 1 To Sheets("Graphs").ChartObjects.Count
Sheets("Graphs").ChartObjects(i).Activate
' Process pivot Table Charts named Appln and Appr
PivotError = IsError(ActiveChart.PivotLayout.PivotTable.Name)
If InStr(ActiveChart.PivotLayout.PivotTable.Name, "Appln") > 0 _
Or InStr(ActiveChart.PivotLayout.PivotTable.Name, "Appr") > 0 Then
'Do domething
End If
' Process Pivot Charts Rates

SkipErrorHandler:
Resume JumpSpot
JumpSpot:
Next i

On Error GoTo 0
End Sub