Handling Errors and MessageBox

[Joel Mills]

Below is a sample of my code. During testing I determined that , there are
two way's the user can create an error; by not retrieving the data for the
data base and by running the Macro a second time. The macro is designed to
create a pivot table on the fly (once). What I need help with is revising
the code to only show the message box if there is an error and then exiting
the sub once they click the okay button on the message box. Now I get the
error message even if no error occurs while running the Sub. After I click
okay it finished the Sub Procedure.

Joel Mills

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Database")


On Error Resume Next

MsgBox "Did you Copy the Export and Run Cleanup?" _
& vbCrLf & "If not then run them before running this Macro"
_
& vbCrLf & " If you have then you must Delete" _
& vbCrLf & "the (Pivot) Worksheet before running this Macro"

Set PT = PTCache.CreatePivotTable(TableDestination:="",
TableName:="PercentTable")

With PT
.PivotFields("Week Ending").Orientation = xlColumnField
.PivotFields("Target Early % Comp.").Orientation = xlDataField
.PivotFields("Target Late % Comp.").Orientation = xlDataField
.PivotFields("Target Planned % Comp.").Orientation = xlDataField
.ColumnGrand = False
.RowGrand = False

End With

ActiveSheet.Name = "Pivot"
End Sub

 

[Guest]

This assumes that you are using command buttons to run your macro's but why
not toggle the enabled property of the button. By default set the property to
false so that the sub can not be run until data is retrieved. When the data
is retrieved from the database then set the property to true. Now the user
can create the pivot with your sub. When the create pivot sub is run set the
property back to false and now it can not be run twice. This should put an
end to your errors. Just a different way to fix the problem.

 

[Joel Mills]

Thanks for the reply Jim. I'm using a drop down menu to run the macros.

 

[Guest]

Try something more like this:

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Database")


On Error Goto PivotError

Set PT = PTCache.CreatePivotTable(TableDestination:="",
TableName:="PercentTable")
On Error Goto 0
With PT
.PivotFields("Week Ending").Orientation = xlColumnField
.PivotFields("Target Early % Comp.").Orientation = xlDataField
.PivotFields("Target Late % Comp.").Orientation = xlDataField
.PivotFields("Target Planned % Comp.").Orientation = xlDataField
.ColumnGrand = False
.RowGrand = False

End With
On Error Goto SheetNameError
ActiveSheet.Name = "Pivot"
Exit Sub
PivotError:
MsgBox "Did you Copy the Export and Run Cleanup?" _
& vbCrLf & "If not then run them before running this Macro"
_
& vbCrLf & " If you have then you must Delete" _
& vbCrLf & "the (Pivot) Worksheet before running this Macro"
exit sub
SheetNameError:
'Not too sure how you want to handle this error...
End Sub

 

[Joel Mills]

Jim thanks for your help. Below is my revised code. It does what I want.

I think it must have been confusing that I have the users delete the Pivot
worksheet. This isn't because of sheet naming problems. It was my way of
making sure the Pivot Table : "PercentTable" didn't already exist and
avoiding an error when creating the Pivot Table a second time.


Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Database")

On Error GoTo PivotError

Set PT = PTCache.CreatePivotTable(TableDestination:="",
TableName:="PercentTable")

On Error GoTo 0

With PT
.PivotFields("Week Ending").Orientation = xlColumnField
.PivotFields("Target Early % Comp.").Orientation = xlDataField
.PivotFields("Target Late % Comp.").Orientation = xlDataField
.PivotFields("Target Planned % Comp.").Orientation = xlDataField
.ColumnGrand = False
.RowGrand = False

End With

ActiveSheet.Name = "Pivot"

Exit Sub

PivotError:
MsgBox "Did you Copy the Export and Run Cleanup?" _
& vbCrLf & "If not then run them before running this Macro"
_
& vbCrLf & " If you have then you must Delete" _
& vbCrLf & "the (Pivot) Worksheet before running this Macro"

End Sub