Dealing with Application Messages when running VBA - Error Handling

Hi,

I have a drop down list that a user can select a period from (Q1,Q2,Q3,Q4). This list drives a named field called "Period". My code fires when the user makes a selection and changes the period on a string of pivots to reflect the selected period.

It works fine as long as the pivots have data/a filter item for the period selected. If it doesn't then excel presents the message "No item of this name exists within Pivot Table report. Rename Q2 to Q4?"

I've not done any error handling yet, so i googled how to use On Error and put something in the code but it doesn't work. Any idea on how i can get around this message and exit the sub with a message?

AB8 and V20 are the cell references of the pivot item list for each pivot table.

Thanks in advance,

J

Please Login or Register  to view this content.

OK, i'll have a look into that - i thought it seemed a bit of a fudge. Thanks!

So would i then need to alter my code to check if the page item exists before i try and change it?

No, they are indeed from separate data sources

actually you can! Deborah (?) at the Contextures blog has some code that will change all pivot field items in every pivot on a worksheet when you use one slicer. I use it on this dashboard.

However, for reasons i won't bother going into, for this circumstance it isn't appropriate.

Thanks for pointing me in the direction of working with the pivot page items, found the following code (thank you again Deborah) which i have reworked for my needs.

However, the underlying year value (i.e. the named range "year") - still changes, even if the values on the pivots do not - this puts them out of kilter. My next step is to figure out how to preserve the year until all the checks have been passed. But for now, thanks for the help.

Please Login or Register  to view this content.

Right! Well, i've got it all sorted now. Up and running so thanks!