| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page field and array selection
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 -- Sharon |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
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 "Sharon" wrote in message ... 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 -- Sharon |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
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 -- Sharon "Bernie Deitrick" wrote: 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 "Sharon" wrote in message ... 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 -- Sharon |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Sharon" wrote in message ... 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 -- Sharon |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
Bernie, I know I am being more than usually stupid, but what is ShowMe? Is
it a variable? -- Sharon "Bernie Deitrick" wrote: 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Sharon" wrote in message ... 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 -- Sharon |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
Sharon,
ShowMe would be a boolean: Dim ShowMe As Boolean Sorry. Bernie MS Excel MVP "Sharon" wrote in message ... Bernie, I know I am being more than usually stupid, but what is ShowMe? Is it a variable? -- Sharon "Bernie Deitrick" wrote: 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Sharon" wrote in message ... 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 -- Sharon |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
Hi Bernie
I worked it out (doh!!) and it works - thank you so much. You are a lifesaver! Regards Sharon -- Sharon "Bernie Deitrick" wrote: 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Sharon" wrote in message ... 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 -- Sharon |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
Life-saving code is a specialty of the house ;-) Bernie MS Excel MVP I worked it out (doh!!) and it works - thank you so much. You are a lifesaver! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
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 "Sharon" wrote: Hi Bernie I worked it out (doh!!) and it works - thank you so much. You are a lifesaver! Regards Sharon -- Sharon "Bernie Deitrick" wrote: 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Sharon" wrote in message ... 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 -- Sharon |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Page field and array selection
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. -- Sharon "Tom Ogilvy" wrote: 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 "Sharon" wrote: Hi Bernie I worked it out (doh!!) and it works - thank you so much. You are a lifesaver! Regards Sharon -- Sharon "Bernie Deitrick" wrote: 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Sharon" wrote in message ... 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 -- Sharon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Synchronize the Pivot table page field selection in 2 tables? | Excel Worksheet Functions | |||
| fit to one page selection creates one page for each cell | Excel Discussion (Misc queries) | |||
| Selection to Array and Array to Sheet | Excel Programming | |||
| Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis | Charts and Charting in Excel | |||
| PivotTable Field Selection | Excel Programming | |||
Hybrid Mode
