Page field and array selection

[Guest]

Hi

I have a series of pivots where the user wants to select a number of options
and have the pivot fields automatically filter to those options. The user
selections are being captured in an array. However, I don't know how to set
the page filters so that they show all of the selected items. The following
code just results in the last array item being used as the page filter. Any
help would be very much appreciated.

For Each pi In pf.PivotItems
For b& = 1 To a&
If pi.Value = aState$(b&) Then
pi.Visible = True
Else
pi.Visible = False
End If
Next b&
Next pi

 

[Bernie Deitrick]

Sharoan,

I think your problem may be indexing: it appears that your aState array may not 'line up' with the
pivotitmes array.

Perhaps, try this:

For Each pi In pf.PivotItems
For b& = 1 To a&
ShowMe = False
For j = lbound(astate$) to ubound(astate$)
if pi.value = astate$(j) then
ShowMe = True
end if
next j
pi.Visible = ShowMe
Next b&
Next pi


HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie for coming back so quickly! I'm tearing my hair out here.
I'll give it a go and let you know what happens. Many thanks again

 

[Bernie Deitrick]

Sharon,

I'm sorry: I should have taken your b& indexing out:

For Each pi In pf.PivotItems
ShowMe = False
For j = lbound(astate$) to ubound(astate$)
if pi.value = astate$(j) then
ShowMe = True
end if
next j
pi.Visible = ShowMe
Next pi

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie, I know I am being more than usually stupid, but what is ShowMe? Is
it a variable?

 

[Guest]

Hi Bernie

I worked it out (doh!!) and it works - thank you so much. You are a
lifesaver!

Regards
Sharon

 

[Bernie Deitrick]

Sharon,

ShowMe would be a boolean:

Dim ShowMe As Boolean

Sorry.

Bernie
MS Excel MVP

Bernie, I know I am being more than usually stupid, but what is ShowMe? Is
it a variable?

 

[Bernie Deitrick]

Life-saving code is a specialty of the house ;-)

Bernie
MS Excel MVP

 

[Guest]

Just a thought and it may never happen, but you could end up in a situation
where your code tries to hide the last visible pivotitem. If so, this will
raise and error.

You might want to add code at the start that makes all pivot items visible
before this loop or faster, make the first pivotitem visible - keep track of
what it really should be and after the loop, hide it if that is what it
should be.

--
Regards,
Tom Ogilvy

Hi Bernie

I worked it out (doh!!) and it works - thank you so much. You are a
lifesaver!

Regards
Sharon

 

[Guest]

Hi Tom - had done that so I must be learning something.!! Thanks for the
advice. Am now trying to achieve all of the above on cube fields so more
anguish, etc. etc.

Thanks a lot to you both.