I'm having a Pivot table in a sheet and I want to set a PivotField at
predefined value when that value exists in the fields list.

The following code works fine but gives an error (-Run-time erro
'1004': Unable to get the PivotTables property of the Worksheet class-
when the predefined value does not exists in the fields list...

How can I transform this into a if-else construction where I check th
presence of the predefined value first? (If predefined value isn'
available then the option '(blank)' should be selected.)


Code
-------------------

ActiveSheet.PivotTables("PivotCC").PivotFields("Cu stomerspecs"). _
CurrentPage = "CC missing"

-------------------

--
s80N
-----------------------------------------------------------------------
s80NL's Profile: http://www.excelforum.com/member.php...fo&userid=3637
View this thread: http://www.excelforum.com/showthread.php?threadid=56159

You can loop through all the items in that pivot table field, like this:

Function CheckFld(TestStr As String) As Boolean
Dim x As Long
For x = 1 To
ActiveSheet.PivotTables("PivotCC").PivotFields("Cu stomerspecs").PivotItems.Count
If
ActiveSheet.PivotTables("PivotCC").PivotFields("Cu stomerspecs").PivotItems(x)
= TestStr$ Then
CheckFld = True
Exit Function
End If
Next x
CheckFld = False
End Function

You can call this function to find out if a particular value exists in the
Cusomerspecs field:

If CheckFld("CC missing") Then
ActiveSheet.PivotTables("PivotCC").PivotFields("Cu stomerspecs"). _
CurrentPage = "CC missing"
End If

Hope this helps,

Hutch

"s80NL" wrote:


I'm having a Pivot table in a sheet and I want to set a PivotField at a
predefined value when that value exists in the fields list.

The following code works fine but gives an error (-Run-time error
'1004': Unable to get the PivotTables property of the Worksheet class-)
when the predefined value does not exists in the fields list...

How can I transform this into a if-else construction where I check the
presence of the predefined value first? (If predefined value isn't
available then the option '(blank)' should be selected.)


Code:
--------------------

ActiveSheet.PivotTables("PivotCC").PivotFields("Cu stomerspecs"). _
CurrentPage = "CC missing"

--------------------


--
s80NL
------------------------------------------------------------------------
s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374
View this thread: http://www.excelforum.com/showthread...hreadid=561596

Tom,

If made the following out of your suggestion:


Formula:
--------------------
Function CheckFld(WS As String, PT As String, PF As String, OptionX As String) As Boolean
Dim i As Long

Sheets(WS).Select
For i = 1 To ActiveSheet.PivotTables(PT).PivotFields(PF).PivotI tems.Count
If ActiveSheet.PivotTables(PT).PivotFields(PF).PivotI tems(i) = OptionX$ Then
CheckFld = True
Exit Function
End If
Next i
CheckFld = False
End Function
Sub RefreshSettings()
For i = 2 To 8
Dim WS As String, PT As String, PF As String, Option1 As String, Option2 As String, Option3 As String
WS = s0.Range("A" & i).Value
PT = s0.Range("B" & i).Value
PF = s0.Range("C" & i).Value
Option1 = s0.Range("D" & i).Value
Option2 = s0.Range("E" & i).Value
Option3 = s0.Range("F" & i).Value

Sheets(WS).Select
If CheckFld(WS, PT, PF, Option1) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).Curren tPage = Option1
ElseIf CheckFld(WS, PT, PF, Option2) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).Curren tPage = Option2
ElseIf CheckFld(WS, PT, PF, Option3) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).Curren tPage = Option3
End If
Next i
End Sub
--------------------


As you can see I've put all information regarding WorkSheet names,
PivotTable names, PivotField names and 3 options in a sheet named s0.

But now I get an error for: -For i = 1 To
ActiveSheet.PivotTables(PT).PivotFields(PF).PivotI tems.Count-

Run-time error '1004': Unable to get the PivotFields property of the
PivotTables class

Any idea what is going wrong and why? Thanks for your help!


--
s80NL


------------------------------------------------------------------------
s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374
View this thread: http://www.excelforum.com/showthread...hreadid=561596

I suspect the problem is in your data. Could you have misspelled a field name
(trailing spaces, etc.) or mismatched a field with a pivot table?

I tested your code with a pivot table and it worked fine.

Regards,

Hutch

"s80NL" wrote:


Tom,

If made the following out of your suggestion:


Formula:
--------------------
Function CheckFld(WS As String, PT As String, PF As String, OptionX As String) As Boolean
Dim i As Long

Sheets(WS).Select
For i = 1 To ActiveSheet.PivotTables(PT).PivotFields(PF).PivotI tems.Count
If ActiveSheet.PivotTables(PT).PivotFields(PF).PivotI tems(i) = OptionX$ Then
CheckFld = True
Exit Function
End If
Next i
CheckFld = False
End Function
Sub RefreshSettings()
For i = 2 To 8
Dim WS As String, PT As String, PF As String, Option1 As String, Option2 As String, Option3 As String
WS = s0.Range("A" & i).Value
PT = s0.Range("B" & i).Value
PF = s0.Range("C" & i).Value
Option1 = s0.Range("D" & i).Value
Option2 = s0.Range("E" & i).Value
Option3 = s0.Range("F" & i).Value

Sheets(WS).Select
If CheckFld(WS, PT, PF, Option1) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).Curren tPage = Option1
ElseIf CheckFld(WS, PT, PF, Option2) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).Curren tPage = Option2
ElseIf CheckFld(WS, PT, PF, Option3) = True Then
ActiveSheet.PivotTables(PT).PivotFields(PF).Curren tPage = Option3
End If
Next i
End Sub
--------------------


As you can see I've put all information regarding WorkSheet names,
PivotTable names, PivotField names and 3 options in a sheet named s0.

But now I get an error for: -For i = 1 To
ActiveSheet.PivotTables(PT).PivotFields(PF).PivotI tems.Count-

Run-time error '1004': Unable to get the PivotFields property of the
PivotTables class

Any idea what is going wrong and why? Thanks for your help!


--
s80NL


------------------------------------------------------------------------
s80NL's Profile: http://www.excelforum.com/member.php...o&userid=36374
View this thread: http://www.excelforum.com/showthread...hreadid=561596