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

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

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!

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

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

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