Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable Drop-Down Un-Checked Item Missing | Excel Worksheet Functions | |||
Data values WON'T DELETE in PivotTable drop-downs | Excel Discussion (Misc queries) | |||
Excel 2000 / How clear drop-down list in Pivottable? | Excel Programming | |||
Dynamic Selection of Pivottable Drop Down Items | Excel Programming | |||
Help - PivotTable Drop-down not Sorting | Excel Programming |