Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables and Macros
Hi,
I'm a newbie at pivot tables, but have been asked by my boss to create one for a bunch of end users who are even less computer literate than him! My pivot table is set up and ready to go, but I have 4 possible "value" fields that a user might want to see summarised within the pivot table. What I would like is to have a drop down box with the 4 possible values in, and everytime a user selects one - say "Actual Balance Outstanding" then it uses that data field within the pivot table. I can set up the drop down box using a validated list, but I am at a loss as to how I can then automate the field selection. Any suggestions would be really appreciated! Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables and Macros
Here is some code for you. I am assuming you have only 1 table in your sheet
and that you drop down is in G1. Also you will need to change the page field name from "This" to whatever you have... Private Sub Worksheet_Change(ByVal Target As Range) Dim pvt As PivotTable Dim pvi As PivotField If Target.Address < "$G$1" Then Exit Sub 'Validation list in G1 Set pvt = Target.Parent.PivotTables(1) 'Change the table? Set pvi = pvt.PageFields.Item("This") 'Change Page Filed Name pvi.CurrentPage = Target.Value End Sub This code will go directly in the sheet. Right click the sheet tab select view code... -- HTH... Jim Thomlinson "Niceaction" wrote: Hi, I'm a newbie at pivot tables, but have been asked by my boss to create one for a bunch of end users who are even less computer literate than him! My pivot table is set up and ready to go, but I have 4 possible "value" fields that a user might want to see summarised within the pivot table. What I would like is to have a drop down box with the 4 possible values in, and everytime a user selects one - say "Actual Balance Outstanding" then it uses that data field within the pivot table. I can set up the drop down box using a validated list, but I am at a loss as to how I can then automate the field selection. Any suggestions would be really appreciated! Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables and Macros
Not sure that Jim addressed your question......
I think what you want is to choose a data fieldfrom a validated list of data fields and then have ypur pivot pick up your choice and change its data field. If so, the code you need is as follows: (right click your sheet tab and click on view code - paste the following into the Worksheet_change event) Dim pvt As PivotTable Dim pvtf As PivotField Const VALIDATED_CELL_NAME As String = "DataNames" If Target.Address = Range(VALIDATED_CELL_NAME).Address Then Application.ScreenUpdating = False Set pvt = Target.Parent.PivotTables(1) Set pvtf = pvt.DataFields(1) If pvtf.SourceName < Target.Value Then pvt.PivotFields(Target.Value).Orientation = xlDataField pvtf.Orientation = xlHidden End If Application.ScreenUpdating = True End If Set pvtf = Nothing Set pvt = Nothing All you need to do then is to name your validation cell (I have used 'DataNames') and then copy this name into the constant definition in the code instead of 'DataNames' "Niceaction" wrote: Hi, I'm a newbie at pivot tables, but have been asked by my boss to create one for a bunch of end users who are even less computer literate than him! My pivot table is set up and ready to go, but I have 4 possible "value" fields that a user might want to see summarised within the pivot table. What I would like is to have a drop down box with the 4 possible values in, and everytime a user selects one - say "Actual Balance Outstanding" then it uses that data field within the pivot table. I can set up the drop down box using a validated list, but I am at a loss as to how I can then automate the field selection. Any suggestions would be really appreciated! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do macros not like pivot tables? | Excel Programming | |||
Pivot Tables in Macros | Excel Programming | |||
Pivot tables and macros | Excel Discussion (Misc queries) | |||
macros and pivot tables... help please! | Excel Programming | |||
macros and pivot tables... help please! | Excel Programming |