Error Handling Issues - Pls Help

[Tim879]

Can anyone help me understand why this does not work if there are no
pivots on the current sheet. this code is supposed to either use the
pivot the user selected or the 1st pivot on the active sheet and then
update the source data. It works perfectly except when a work sheet
has no pivots - in that case, it creates an error that I cannot catch.

Why does the line On Error GoTo Error_Need_Pivot_Source in the
NoPivotSelected: block not find this error? How can I correct it?

Any help would be greatly appreciated.

thanks
Tim

Sub Update_Pivot_Table_Sources()
'
' Macro recorded 5/31/2007 by TB
'
'
Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, _
strCurrentSheet As String, _
strNewPivotTblSrc As String, _
strResponse As String
Dim pt As PivotTable

strResponse = MsgBox("Do you want to change all of the Pivot Table
Sources?", vbOKCancel)

If strResponse <> vbOK Then MsgBox ("Cancelled")

If strResponse = vbOK Then

'see if the user selected a pivot table. if so, assign it to pt and
get the source
On Error GoTo NoPivotSelected
Set pt = ActiveCell.PivotTable
CurPivotTblSrc = pt.SourceData
GoTo Found_Pivot_Source

'if the user didn't select a pivot, see if there is one on the active
sheet.
'If so, use that. If not, return an error and exit
NoPivotSelected:

On Error GoTo Error_Need_Pivot_Source
CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData
GoTo Found_Pivot_Source

Found_Pivot_Source:

strNewPivotTblSrc = InputBox("Please enter the new source for the
pivot table below", "New Pivot Source", CurPivotTblSrc)

If strNewPivotTblSrc = "" Then
MsgBox ("Cancelled")
GoTo Exit_Update_All_Pivots
End If


strResponse = MsgBox("Do you want to update all pivots (click Yes) or
just pivot tables with this data source: " _
& CurPivotTableSrc & " (click no)", vbYesNo)


On Error GoTo Error_Found

Application.ScreenUpdating = False

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_Update_All_Pivots

For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
'this worksheet one at a time
For Each pt In ActiveSheet.PivotTables

If strResponse = vbNo Then
If pt.SourceData = CurPivotTblSrc Then
pt.SourceData = strNewPivotTblSrc
ActiveWorkbook.ShowPivotTableFieldList = False
End If
Else
pt.SourceData = strNewPivotTblSrc
ActiveWorkbook.ShowPivotTableFieldList = False
End If
Next

'turn warning messages on
Application.DisplayAlerts = True
Next

'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet).Activate

MsgBox ("Pivots updated successfully")

End If

GoTo Exit_Update_All_Pivots

Error_Found:
MsgBox ("Error Found. Macro ending. " & Err & ": " &
Error(Err))
GoTo Exit_Update_All_Pivots

Error_Need_Pivot_Source:
MsgBox ("Cannot find pivot table. Please select a sheet with a
pivot and re-run macro")
GoTo Exit_Update_All_Pivots

Exit_Update_All_Pivots:
Application.CommandBars("PivotTable").Visible = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

 

[Bernie Deitrick]

Tim,

You need a Resume statement, Replace this:

NoPivotSelected:

With this:

NoPivotSelected:
Resume RightHere
RightHere:


HTH,
Bernie
MS Excel MVP

 

[Roger Converse]

Hello,

I am trying to incorporate this macro logic into one of my spreadsheets. I
am much more familiar with Access, than Excel so here goes...

My question is when I run this, and get to the question on the source data
the default option is data source sheet!C1:C18. If I change that, the macro
does not run without error. If I leave the default value in the response
box, the will run. The error I receive states PivotTable field name is not
valid. Is this because the Pivot Table that I am trying to refresh is summed
or something or am I just entering the range incorrectly. Also, I can't
quite put my finger on how the default is selected.

Specifically, in the CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData
statement below.

The way I read that is that the SourceData for the pivot table on the active
sheet should select everything, so why is it only showing cloumns C1-C18?

On Error GoTo Error_Need_Pivot_Source
CurPivotTblSrc = ActiveSheet.PivotTables(1).SourceData
GoTo Found_Pivot_Source

Found_Pivot_Source:

strNewPivotTblSrc = InputBox("Please enter the new source for the" _
& " pivot table below", "New Pivot Source", CurPivotTblSrc)

Any help is appreciated.

 

[Chip Pearson]

If you are trying to incorporate the code listed in the original post with
the solution to that problem provided by Bernie, you need to understand two
basic things about GoTo statements. The first form of a GoTo is simply:

GoTo TheLabel:
''
' code
''
TheLabel:
""
' more code
''
ErrHandler:
''
' error handler block
''
End Sub

This could be called a simple "code jump" GoTo. This simple type of GoTo
merely transfers code execution over one block of code so execution
continues at a new line. This sort of GoTo is frowned upon by professional
developers. While there is nothing wrong with GoTo per se, its use does
often lead to "spaghetti code", code with lots up jumps with GoTos. This
makes the code difficult to understand and especially difficult to maintain.
As a general rule, if you are using GoTo to skip over a block of code, that
block of code should be in a Sub or Function procedure to be called only
some condition is true. For example,

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Bad GoTo
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''
' some code
''
If X<>Y Then
GoTo TheLabel:
End If
''
' conditionally called code, runs only if X=Y
''
TheLabel:
''
' some code
''

This is better written as
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Better Structured Code
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''
' Good Structured code
'''
'''
' some code
''
If X=Y Then
Call SomeProc ' called only if X=Y
End If
''
' more code
''
''
' some code
''

This removes the GoTo statement and calls SomeProc (which replaces the
Conditionally Called Code of the first example). Not only is this easier to
understand and maintain, it also promotes the very good practice of code
re-use. It is quite possible that you can use the SomeProc procedure in
several different areas of the code, meaning you write and test it once and
you don't have to re-invent the wheel each time such code is needed.

Using the GoTo statement in an On Error Goto XXX statement is quite
different than a simple jump Goto. In this case, a run time error occurs and
execution results at the XXX label. However, VBA is still running in "error
handling mode". In this mode, no subsequent errors are handled by the On
Error statement. You can't trap error when VBA is in "error handling mode".
Thus, code like

''
' Some Code
''
On Error Resume Label1:
''
' more code
''
Exit Sub
Label1:
Debug.Print CStr(Err.Number), Err.Description
' for illustration, for another error
Debug.Print 1/0
End Sub

In this code, an error in "more code" will transfer execution to the Label1:
marker. However, VBA is running in "error handler mode" and will not trap
any subsequent errors. In the example code, we use 1/0 to force a #DIV/0
error. Here, the On Error Resume statement will NOT have any effect. In
order to re-establish error handling, your code must use a Resume statement
to pass execution to some other point. The Resume statement (like the End
Sub or End Function or End Property statements) turns off the "error
handling mode" and resets it to "standard mode". Error handling mode is
also turned off and restored to normal mode when the procedure containing
the "On Error Goto XXX" terminates, either naturally or prematurely with an
Exit Sub, Exit Function, or Exit Property statement.

Error handling can be tricky especially when you have multiple procedures
that call one another and some of those procedures had error handling code
and others do not. See http://www.cpearson.com/Excel/ErrorHandling.htm for
a fairly comprehensive discussion of error handling. I think everything will
be improved once VB/NET is fully integrated with Office so instead of On
Error statements you can use Try/Catch/Finally blocks to handler errors.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)