Trap No Item of this name exits in this Pivot Table

Ziggy12

Active Member
Joined
Jun 26, 2002
Messages
361
How do ITrap "No Item of this name exits in this Pivot Table. Rename "XXXX" to "%$$#&"
(and cause a whole pile of problems)

I have some code selecting page feilds in two pivot tables by entering directly into the pivot page feilds. The available list of items is derived from one main pivot table. Unfortunatly the item is not always in the other pivottable pagefeild. When that happens Excel offers to overwrite the current value with the one selected. I don't want to give the users oppourtunity to do this.

How can I trap the occurance of an item not being in a pivot or trap appearance of the dialog.

cheers
ziggy
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
add this to you current sub after wherever the overwrite code appears: "msgbox err.number"

Then go back and add:
"On Error Goto Label"
"Label:
If Err.Number = X Then
Resume Next"

?
 

Ziggy12

Active Member
Joined
Jun 26, 2002
Messages
361
That works when the pivot page feild is (All). Still get the no name exists warning when the page geild is set to something other than all.

Must be a before warning event or something somewhere. Any ideas?

ziggy
 

Ziggy12

Active Member
Joined
Jun 26, 2002
Messages
361
One solution
Set all pagefeilds to (ALL ) before changing them with the macro

Sub ResetPivtPgeFlds()
Dim WrkSheet As Worksheet
Dim Pvttable As PivotTable
Dim PvtField As PivotField
Dim Pvtitem As PivotItem

For Each WrkSheet In Worksheets
For Each Pvttable In WrkSheet.PivotTables
For Each PvtField In Pvttable.PageFields
PvtField.CurrentPage = "(All)"
Next: Next: Next
End Sub

There's is probably away of checking to see if the selection is available in the pagefield first e.g.
for each Pvtitem in Pvttable pagefields
if Pvtfield.currentpage=(Selection) then Pvtfield.currentpage=(Selection) else Msgbox("selectionnot available)

sort of thing

cheers
Ziggy
 

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
I'm sure there is, because I don't receive any such message. It might have to do with your locale settings too.

I'm actually dealing with exactly the same problem right now... where I have things not present in one pivot table but present in another, and I need to be able to check before selectin. Only what I really want is to select, not overwrite the items in the field.

You can go:

For Each Item in PivotTables("name").PivotFields("field name")
If Item.Name = "comparison text" Then: 'code
Next

But I have another question.

Do you know, is "currentpage =" (what you get when you turn on the macro recorder and select a pivot item from a pivot field) the only way to select something from a pivot field? PivotFields("").Select = "text"?
 

Ziggy12

Active Member
Joined
Jun 26, 2002
Messages
361
Think your on the right track with the if its here then do something

What I've done is select page feilds with a user form driven off a look up table that only allows user to select valid combinations.

e.g. if comboox1 = XXXX then only items associated with XXXX with be available for selection in combobox2. and then use the userform combobox values to post valid values into the pivottable - well the one that has all the entries anyway.

Setting all pivottables (there are three) pagefeilds to ALL on userform initialization and then trapping the error (err=1004) when non available pagefeild is attempted has solved the problem but I'm sure there'll be a much more elegant way.

Apart from writting a value into a pagefeild cell currentpage will be the only way to change pagefeilds.

cheers
ziggy
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,600
Messages
5,832,650
Members
430,151
Latest member
Kacbear

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top