Pivot Table - Multiple Pivot Field Selection
 

Hi All,

I have a quick questions that I believe can be answered
quickly.

I have a PivotTable with the following Pivot Fields:

GROUP
NAME
DIV

What I'm wondering is whether I can select a Pivot Item
from a combination of two fields values. For instance, I
need to add items to a new collection where the item in
the "GROUP" field matches a previously selected value.

In simplist form, i need to add all items in the "NAME"
field where the "GROUP" field matches the previously
selected value.

I'm sure that the items index will help, but i can only
stay within the nominated pivot field.

Sample Code: (If it helps)

Set oPiv = frmNewProgramDetails.oPiv ' From Unfocused
Form)
oSelectedGroup = frmNewProgramDetails.cmbGroup ' Value to
match
For Each Item In oPiv.PivotFields("NAME").PivotItems
If oPiv.PivotField("GROUP").PivotItem(Item) =
oSelectedGroup Then
'I want it to add to the colListItems only if the "GROUP"
= oSelectedGroup
colListItems.Add Item
End If
Next Item


Hope someone can shed some light on this.

Thanks in advance,

Paul Mac

Pivot Table - Multiple Pivot Field Selection
 

Items are visible or not. You don't add them. The item list is derived
from the source data.

So loop through your items and make them either visible = true or visible =
false.

This is a guess on what you are trying to do as you explanation was not
clear to me.

--
Regards,
Tom Ogilvy


Paul Mac. wrote in message
...
Hi All,

I have a quick questions that I believe can be answered
quickly.

I have a PivotTable with the following Pivot Fields:

GROUP
NAME
DIV

What I'm wondering is whether I can select a Pivot Item
from a combination of two fields values. For instance, I
need to add items to a new collection where the item in
the "GROUP" field matches a previously selected value.

In simplist form, i need to add all items in the "NAME"
field where the "GROUP" field matches the previously
selected value.

I'm sure that the items index will help, but i can only
stay within the nominated pivot field.

Sample Code: (If it helps)

Set oPiv = frmNewProgramDetails.oPiv ' From Unfocused
Form)
oSelectedGroup = frmNewProgramDetails.cmbGroup ' Value to
match
For Each Item In oPiv.PivotFields("NAME").PivotItems
If oPiv.PivotField("GROUP").PivotItem(Item) =
oSelectedGroup Then
'I want it to add to the colListItems only if the "GROUP"
= oSelectedGroup
colListItems.Add Item
End If
Next Item


Hope someone can shed some light on this.

Thanks in advance,

Paul Mac

Pivot Table - Multiple Pivot Field Selection
 

Thanks for the reply Tom,

I understand the visible property, so I will try to explain my question more
concisely.

What I need to be able to do is return the items from the Pivot Table, where
they match a user selection. I will then add each of the items to a New
Collection, from which I will populate a list box of a user form. This will
in effect offer a list of the unique values in that group. Normally I would
do this by having a Unique Collection routine from the selected (entire
range(a1:a1790)), but in the sake of preserving computations on the form
initalize routine, I thought that this information could be easily returned
from the Pivot Table.

It will not display the results in a Pivot Table (Visible or not), all I
want is the items to use elsewhere.

So to illistrate the following is the mock Pivot Table:
Group Name
Training | ASMSF
| Super Products
| Fortified Income Protection
| Watching Your Garden Grow
| Risk Assement Series
Symposiums | Corporate Governance Symposium
| Local Government Symposium
Congress | ACT Congress 2004
| VIC Congress

So, on the user form, the user selects the Group, I would like to return all
the items within that group.

If they were to select "Training", then I would like to add the following
items to a New Collection: ASMSF, Super Products, Fortified Income
Protection, Watching Your Garden Grow & Risk Assesment Series.
This would then be used to populate a list box, by adding all the items
from the collection. So I know that by looping through the "Name" Field, I
will get all of the items, but how can I see what "Group" they are listed
under?

Hopefully this makes the requirements more clear. I look forward to your
response.

Thanks again for your help.

Paul.

"Tom Ogilvy" wrote in message
...
Items are visible or not. You don't add them. The item list is derived
from the source data.

So loop through your items and make them either visible = true or visible
=
false.

This is a guess on what you are trying to do as you explanation was not
clear to me.

--
Regards,
Tom Ogilvy


Paul Mac. wrote in message
...
Hi All,

I have a quick questions that I believe can be answered
quickly.

I have a PivotTable with the following Pivot Fields:

GROUP
NAME
DIV

What I'm wondering is whether I can select a Pivot Item
from a combination of two fields values. For instance, I
need to add items to a new collection where the item in
the "GROUP" field matches a previously selected value.

In simplist form, i need to add all items in the "NAME"
field where the "GROUP" field matches the previously
selected value.

I'm sure that the items index will help, but i can only
stay within the nominated pivot field.

Sample Code: (If it helps)

Set oPiv = frmNewProgramDetails.oPiv ' From Unfocused
Form)
oSelectedGroup = frmNewProgramDetails.cmbGroup ' Value to
match
For Each Item In oPiv.PivotFields("NAME").PivotItems
If oPiv.PivotField("GROUP").PivotItem(Item) =
oSelectedGroup Then
'I want it to add to the colListItems only if the "GROUP"
= oSelectedGroup
colListItems.Add Item
End If
Next Item


Hope someone can shed some light on this.

Thanks in advance,

Paul Mac

Pivot Table - Multiple Pivot Field Selection
 

Explanation seems much clearer.

Probably the easiest would be to look at the pivot Table cells on the
spreadsheet itself. I could be wrong, but I don't think there is an easy
way to get it from the pivot table objects.

the alternative would be to pick up your source data in an array (in on
command) and build loop through that array to build the collection.

--
Regards,
Tom Ogilvy


"Paul M" wrote in message
...
Thanks for the reply Tom,

I understand the visible property, so I will try to explain my question
more
concisely.

What I need to be able to do is return the items from the Pivot Table,
where
they match a user selection. I will then add each of the items to a New
Collection, from which I will populate a list box of a user form. This
will
in effect offer a list of the unique values in that group. Normally I
would
do this by having a Unique Collection routine from the selected (entire
range(a1:a1790)), but in the sake of preserving computations on the form
initalize routine, I thought that this information could be easily
returned
from the Pivot Table.

It will not display the results in a Pivot Table (Visible or not), all I
want is the items to use elsewhere.

So to illistrate the following is the mock Pivot Table:
Group Name
Training | ASMSF
| Super Products
| Fortified Income Protection
| Watching Your Garden Grow
| Risk Assement Series
Symposiums | Corporate Governance Symposium
| Local Government Symposium
Congress | ACT Congress 2004
| VIC Congress

So, on the user form, the user selects the Group, I would like to return
all
the items within that group.

If they were to select "Training", then I would like to add the following
items to a New Collection: ASMSF, Super Products, Fortified Income
Protection, Watching Your Garden Grow & Risk Assesment Series.
This would then be used to populate a list box, by adding all the items
from the collection. So I know that by looping through the "Name" Field, I
will get all of the items, but how can I see what "Group" they are listed
under?

Hopefully this makes the requirements more clear. I look forward to your
response.

Thanks again for your help.

Paul.

"Tom Ogilvy" wrote in message
...
Items are visible or not. You don't add them. The item list is derived
from the source data.

So loop through your items and make them either visible = true or
visible
=
false.

This is a guess on what you are trying to do as you explanation was not
clear to me.

--
Regards,
Tom Ogilvy


Paul Mac. wrote in message
...
Hi All,

I have a quick questions that I believe can be answered
quickly.

I have a PivotTable with the following Pivot Fields:

GROUP
NAME
DIV

What I'm wondering is whether I can select a Pivot Item
from a combination of two fields values. For instance, I
need to add items to a new collection where the item in
the "GROUP" field matches a previously selected value.

In simplist form, i need to add all items in the "NAME"
field where the "GROUP" field matches the previously
selected value.

I'm sure that the items index will help, but i can only
stay within the nominated pivot field.

Sample Code: (If it helps)

Set oPiv = frmNewProgramDetails.oPiv ' From Unfocused
Form)
oSelectedGroup = frmNewProgramDetails.cmbGroup ' Value to
match
For Each Item In oPiv.PivotFields("NAME").PivotItems
If oPiv.PivotField("GROUP").PivotItem(Item) =
oSelectedGroup Then
'I want it to add to the colListItems only if the "GROUP"
= oSelectedGroup
colListItems.Add Item
End If
Next Item


Hope someone can shed some light on this.

Thanks in advance,

Paul Mac