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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,502
Messages
5,523,295
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top