Handling Errors and MessageBox
 

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:=xlDatab ase,
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

Handling Errors and MessageBox
 

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.
--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

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:=xlDatab ase,
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

Handling Errors and MessageBox
 

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


"Jim Thomlinson" wrote in message
...
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.
--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

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:=xlDatab ase,
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

Handling Errors and MessageBox
 

Try something more like this:

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

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
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

--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

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


"Jim Thomlinson" wrote in message
...
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.
--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

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:=xlDatab ase,
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

Handling Errors and MessageBox
 

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:=xlDatab ase,
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

"Jim Thomlinson" wrote in message
...
Try something more like this:

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

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
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

--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

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


"Jim Thomlinson" wrote in message
...
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.
--
HTH...

Jim Thomlinson


"Joel Mills" wrote:

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:=xlDatab ase,
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