Trap No Item of this name exits in this Pivot Table

[Ziggy12]

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

 

[Glory]

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]

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]

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]

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]

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