Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotfields.CurrentPage
Hi!
I have a spreadsheet with some dropdown boxes where you can make selections which will then filter the data for the regular calculations. There is also a Pivot Chart which I'm linking to the same dropdown boxes by manipulating the pages to set to the same values by using CurrentPage. That works fine, but how do I catch when the user selects nothing from the dropdown? Currently I set the CurrentPage to "(all)" which does the trick, but this workbook is used in other countries with non-english installations, where the pivotfield instead will say "(toutes)","(alle)", etc. How do I solve it for them? I have tried finding some form of Index I could set (Currentpage.Index=0 for instance) unsuccessfully. Or is there a way to find out what "(all)" is called in the local installation? Brgds Danny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotfields.CurrentPage
You could check for a parenthesis at the left of the string. For example:
'================================ Private Sub Worksheet_Change(ByVal Target As Range) Dim pt As PivotTable Dim pf As PivotField Dim str As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PageFields(1) str = Left(pt.PivotFields(pf.Name).CurrentPage, 1) If str = "(" Then MsgBox "(All) was selected" Else 'do nothing End If End Sub '================================= Daniel Magnus Bennét Björck wrote: Hi! I have a spreadsheet with some dropdown boxes where you can make selections which will then filter the data for the regular calculations. There is also a Pivot Chart which I'm linking to the same dropdown boxes by manipulating the pages to set to the same values by using CurrentPage. That works fine, but how do I catch when the user selects nothing from the dropdown? Currently I set the CurrentPage to "(all)" which does the trick, but this workbook is used in other countries with non-english installations, where the pivotfield instead will say "(toutes)","(alle)", etc. How do I solve it for them? I have tried finding some form of Index I could set (Currentpage.Index=0 for instance) unsuccessfully. Or is there a way to find out what "(all)" is called in the local installation? Brgds Danny -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotfields.CurrentPage
Hi!
Thank you, but unfortunately I want to do it the other way around. If one particular cell is left blank, I want the pivottable to switch to the "(all)" page. How do I do that? (BTW, your example would select "(blanks)" as well) Brgds Danny *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotfields.CurrentPage
Good point about the (blanks) -- I forgot about those. You could remove
and replace the field from the page area: '========================== Private Sub Worksheet_Change(ByVal Target As Range) Dim wsPT As Worksheet Set wsPT = Worksheets("Pivot") If Target.Count 1 Then Exit Sub If Target.Address = "$H$2" Then If Target.Value = "" Then wsPT.PivotTables(1) _ .PivotFields("Rep").Orientation = xlHidden With wsPT.PivotTables(1) _ .PivotFields("Rep") .Orientation = xlPageField .Position = 1 End With Else wsPT.PivotTables(1).PivotFields("Rep") _ .CurrentPage = Target.Value End If End If End Sub '============================ Daniel Magnus Bennét Björck wrote: Hi! Thank you, but unfortunately I want to do it the other way around. If one particular cell is left blank, I want the pivottable to switch to the "(all)" page. How do I do that? (BTW, your example would select "(blanks)" as well) Brgds Danny *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotfields.CurrentPage
Hi!
Very smart and nifty, and works like a charm too! Thank You. (BTW the field I was specifically targetting was indeed called "Rep"!) Brgds Danny "Debra Dalgleish" wrote in message ... Good point about the (blanks) -- I forgot about those. You could remove and replace the field from the page area: '========================== Private Sub Worksheet_Change(ByVal Target As Range) Dim wsPT As Worksheet Set wsPT = Worksheets("Pivot") If Target.Count 1 Then Exit Sub If Target.Address = "$H$2" Then If Target.Value = "" Then wsPT.PivotTables(1) _ .PivotFields("Rep").Orientation = xlHidden With wsPT.PivotTables(1) _ .PivotFields("Rep") .Orientation = xlPageField .Position = 1 End With Else wsPT.PivotTables(1).PivotFields("Rep") _ .CurrentPage = Target.Value End If End If End Sub '============================ Daniel Magnus Bennét Björck wrote: Hi! Thank you, but unfortunately I want to do it the other way around. If one particular cell is left blank, I want the pivottable to switch to the "(all)" page. How do I do that? (BTW, your example would select "(blanks)" as well) Brgds Danny *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivotfields.CurrentPage
You're welcome, and thanks for letting me know that it worked (and for
confirming my psychic powers!) Daniel Magnus Bennét Björck wrote: Hi! Very smart and nifty, and works like a charm too! Thank You. (BTW the field I was specifically targetting was indeed called "Rep"!) Brgds Danny "Debra Dalgleish" wrote in message ... Good point about the (blanks) -- I forgot about those. You could remove and replace the field from the page area: '========================== Private Sub Worksheet_Change(ByVal Target As Range) Dim wsPT As Worksheet Set wsPT = Worksheets("Pivot") If Target.Count 1 Then Exit Sub If Target.Address = "$H$2" Then If Target.Value = "" Then wsPT.PivotTables(1) _ .PivotFields("Rep").Orientation = xlHidden With wsPT.PivotTables(1) _ .PivotFields("Rep") .Orientation = xlPageField .Position = 1 End With Else wsPT.PivotTables(1).PivotFields("Rep") _ .CurrentPage = Target.Value End If End If End Sub '============================ Daniel Magnus Bennét Björck wrote: Hi! Thank you, but unfortunately I want to do it the other way around. If one particular cell is left blank, I want the pivottable to switch to the "(all)" page. How do I do that? (BTW, your example would select "(blanks)" as well) Brgds Danny *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to get the pivotfields property of the pivottable class | Excel Discussion (Misc queries) | |||
PivotTable - PivotFields VBA | Excel Discussion (Misc queries) | |||
Deleting PivotFields from a PivotChart in VB.NET | Charts and Charting in Excel | |||
Where does Excel store the orientation of PivotFields? | Charts and Charting in Excel |