VBA Excel PivotTable Drop Down Help
 

Hi,

Okay, so I'm primarily an Access/Web developer, but a client has asked
us to use Microsoft Access to automatically set filter values on their
Excel PivotTables. This has been going okay until I hit a snag. Most
of the filters are single value - meaning there is a drop-down list of
(All), Value1, Value2, etc. However, there are a couple which are
multi-select. There is a drown down list, and instead of selecting one
of the items, you can click on a little checkbox to select 1-many of
the items.

I need to figure out how to set THESE items programmatically. Any
ideas?

Thanks!

VBA Excel PivotTable Drop Down Help
 

Sub ShowHidePivotItems()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

' Set reference to pivot table
Set pt = ActiveSheet.PivotTables(1)
' Set reference to the pivot field with dates to show/hide
Set pf = pt.PivotFields("Color")

For Each pi In pf.PivotItems

If pi.Value = "orange" Or pi.Value = "green" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next

End Sub

"Ri" wrote in message
oups.com...
Hi,

Okay, so I'm primarily an Access/Web developer, but a client has asked
us to use Microsoft Access to automatically set filter values on their
Excel PivotTables. This has been going okay until I hit a snag. Most
of the filters are single value - meaning there is a drop-down list of
(All), Value1, Value2, etc. However, there are a couple which are
multi-select. There is a drown down list, and instead of selecting one
of the items, you can click on a little checkbox to select 1-many of
the items.

I need to figure out how to set THESE items programmatically. Any
ideas?

Thanks!

VBA Excel PivotTable Drop Down Help
 

Thanks!

However, when I try and do this, I get an error that says I'm unable to
set the visible property of the item. Any ideas?

Thanks again!

Tim Zych wrote:
Sub ShowHidePivotItems()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

' Set reference to pivot table
Set pt = ActiveSheet.PivotTables(1)
' Set reference to the pivot field with dates to show/hide
Set pf = pt.PivotFields("Color")

For Each pi In pf.PivotItems

If pi.Value = "orange" Or pi.Value = "green" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next

End Sub

"Ri" wrote in message
oups.com...
Hi,

Okay, so I'm primarily an Access/Web developer, but a client has asked
us to use Microsoft Access to automatically set filter values on their
Excel PivotTables. This has been going okay until I hit a snag. Most
of the filters are single value - meaning there is a drop-down list of
(All), Value1, Value2, etc. However, there are a couple which are
multi-select. There is a drown down list, and instead of selecting one
of the items, you can click on a little checkbox to select 1-many of
the items.

I need to figure out how to set THESE items programmatically. Any
ideas?

Thanks!

VBA Excel PivotTable Drop Down Help
 

What is the value of the item, and does that really exist in the data? It's
possible there are old values in the pivotfield list that no longer exist in
the data source.

Try this modified version. It deletes unused pivotitems first.

Sub ShowHidePivotItems()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables(1)

Set pf = pt.PivotFields("Color")

On Error Resume Next
' Remove old items first
For Each pi In pf.PivotItems
pi.Delete
Next
On Error GoTo 0
pt.RefreshTable

For Each pi In pf.PivotItems
If pi.Value = "blue" Then 'Or pi.Value = "green" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next

pt.RefreshTable

End Sub

"Ri" wrote in message
oups.com...
Thanks!

However, when I try and do this, I get an error that says I'm unable to
set the visible property of the item. Any ideas?

Thanks again!

Tim Zych wrote:
Sub ShowHidePivotItems()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

' Set reference to pivot table
Set pt = ActiveSheet.PivotTables(1)
' Set reference to the pivot field with dates to show/hide
Set pf = pt.PivotFields("Color")

For Each pi In pf.PivotItems

If pi.Value = "orange" Or pi.Value = "green" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next

End Sub

"Ri" wrote in message
oups.com...
Hi,

Okay, so I'm primarily an Access/Web developer, but a client has asked
us to use Microsoft Access to automatically set filter values on their
Excel PivotTables. This has been going okay until I hit a snag. Most
of the filters are single value - meaning there is a drop-down list of
(All), Value1, Value2, etc. However, there are a couple which are
multi-select. There is a drown down list, and instead of selecting
one
of the items, you can click on a little checkbox to select 1-many of
the items.

I need to figure out how to set THESE items programmatically. Any
ideas?

Thanks!

VBA Excel PivotTable Drop Down Help
 

Actually, I figured out that I needed to set do this:

pi.AutoSort xlManual, pf.SourceName
'Code Here
pi.AutoSort xlAscending, pf.SourceName

And it worked fine.

Thanks!
Tim Zych wrote:
What is the value of the item, and does that really exist in the data? It's
possible there are old values in the pivotfield list that no longer exist in
the data source.

Try this modified version. It deletes unused pivotitems first.

Sub ShowHidePivotItems()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables(1)

Set pf = pt.PivotFields("Color")

On Error Resume Next
' Remove old items first
For Each pi In pf.PivotItems
pi.Delete
Next
On Error GoTo 0
pt.RefreshTable

For Each pi In pf.PivotItems
If pi.Value = "blue" Then 'Or pi.Value = "green" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next

pt.RefreshTable

End Sub

"Ri" wrote in message
oups.com...
Thanks!

However, when I try and do this, I get an error that says I'm unable to
set the visible property of the item. Any ideas?

Thanks again!

Tim Zych wrote:
Sub ShowHidePivotItems()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

' Set reference to pivot table
Set pt = ActiveSheet.PivotTables(1)
' Set reference to the pivot field with dates to show/hide
Set pf = pt.PivotFields("Color")

For Each pi In pf.PivotItems

If pi.Value = "orange" Or pi.Value = "green" Then
pi.Visible = True
Else
pi.Visible = False
End If
Next

End Sub

"Ri" wrote in message
oups.com...
Hi,

Okay, so I'm primarily an Access/Web developer, but a client has asked
us to use Microsoft Access to automatically set filter values on their
Excel PivotTables. This has been going okay until I hit a snag. Most
of the filters are single value - meaning there is a drop-down list of
(All), Value1, Value2, etc. However, there are a couple which are
multi-select. There is a drown down list, and instead of selecting
one
of the items, you can click on a little checkbox to select 1-many of
the items.

I need to figure out how to set THESE items programmatically. Any
ideas?

Thanks!