Set PivotField to predefined value with check

[s80NL]

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.)

Please Login or Register  to view this content.

[Tom Hutchins]

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("Customerspecs").PivotItems.Count
If
ActiveSheet.PivotTables("PivotCC").PivotFields("Customerspecs").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("Customerspecs"). _
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("Customerspecs"). _
> 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
>
>

[s80NL]

Tom,

If made the following out of your suggestion:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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).PivotItems.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!

[Tom Hutchins]

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:
>
>
> PHP code:
> --------------------
> 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).PivotItems.Count
> If ActiveSheet.PivotTables(PT).PivotFields(PF).PivotItems(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).CurrentPage = Option1
> ElseIf CheckFld(WS, PT, PF, Option2) = True Then
> ActiveSheet.PivotTables(PT).PivotFields(PF).CurrentPage = Option2
> ElseIf CheckFld(WS, PT, PF, Option3) = True Then
> ActiveSheet.PivotTables(PT).PivotFields(PF).CurrentPage = 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).PivotItems.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
>
>