Almost there... dynamic and conditional set of PivotItems
 

Ok, I've got the following situation:

Sheets("Inhoud") or s1, the place where the default values are kept i
cells A1:D10 (A = SheetName, B = PivotTablesName, C = PivotFieldsName
D = the default value)
Sheets("Contracten") or s2, with a PivotTable on it
Sheets("Afgelopen contracten") or s3, with a PivotTable on it
etc

I can set a default value to a PivotFields.PivotItems with this code:


Code
-------------------
' actually works with XP & Excel 2003
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields(" AM")
Dim SPgField2 As Variant
s1.Range("D2").Select
SPgField2 = Selection
.CurrentPage = SPgField2
End Wit
-------------------


But then, it also sets the default value when the value wasn't presen
in the list before!!

I tried to make this piece of code conditional as you can see below...


Code
-------------------
' doesn't work... :S
Dim i As Long
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields(" AM") 'Sheets("Aflopende contracten") = s3
For i = 1 To .PivotItems.Count
If .PivotItems(i) = s1.Range("D2").Select Then
Dim SPgField1 As Variant
s1.Range("D2").Select
SPgField1 = Selection
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
Else
MsgBox ("NO / i=" & i)
End If
Next i
End Wit
-------------------


But that isn't working, it somehow fails to recognize the value in th
if-statement, all I got was the NO message...

Anyone, who knows how to fix this?? I'm running XP with Excel 2003.

Ok, that was my first question. Now the second. How can I make the cod
dynamic so I can use it as a Function? Or how can I get the SheetsName
PivotTablesName and PivotFieldsName from s1 (Sheets("Inhoud")) column
A:C?

It would be awesome if we can get this working ;

--
s80N

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

Almost there... dynamic and conditional set of PivotItems
 

First one is easy
If .PivotItems(i) = s1.Range("D2").Select
should be
If .PivotItems(i) = s1.Range("D2").Value
Second one I'm not sure if I understand. You can always access stuff by
the index. ie. PivotTables(1)
post back with more info.

HTH

Die_Another_Day
s80NL wrote:
Ok, I've got the following situation:

Sheets("Inhoud") or s1, the place where the default values are kept in
cells A1:D10 (A = SheetName, B = PivotTablesName, C = PivotFieldsName,
D = the default value)
Sheets("Contracten") or s2, with a PivotTable on it
Sheets("Afgelopen contracten") or s3, with a PivotTable on it
etc

I can set a default value to a PivotFields.PivotItems with this code:


Code:
--------------------
' actually works with XP & Excel 2003
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields(" AM")
Dim SPgField2 As Variant
s1.Range("D2").Select
SPgField2 = Selection
.CurrentPage = SPgField2
End With
--------------------


But then, it also sets the default value when the value wasn't present
in the list before!!

I tried to make this piece of code conditional as you can see below...


Code:
--------------------
' doesn't work... :S
Dim i As Long
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields(" AM") 'Sheets("Aflopende contracten") = s3
For i = 1 To .PivotItems.Count
If .PivotItems(i) = s1.Range("D2").Select Then
Dim SPgField1 As Variant
s1.Range("D2").Select
SPgField1 = Selection
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
Else
MsgBox ("NO / i=" & i)
End If
Next i
End With
--------------------


But that isn't working, it somehow fails to recognize the value in the
if-statement, all I got was the NO message...

Anyone, who knows how to fix this?? I'm running XP with Excel 2003.

Ok, that was my first question. Now the second. How can I make the code
dynamic so I can use it as a Function? Or how can I get the SheetsName,
PivotTablesName and PivotFieldsName from s1 (Sheets("Inhoud")) columns
A:C?

It would be awesome if we can get this working ;)


--
s80NL


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

Almost there... dynamic and conditional set of PivotItems
 

Absolutely awesome :cool: I've created to following code out of you
remarks, but I've to wait till tomorrow morning to test it at work..
so I'll let you know the results later.

How silly can it be Value instead of Select... Today, I've been starin
at that stupid line for ages :eek:


Code
-------------------
Sub ShouldWorkNow()
' NOT TESTED YET... will test it tomorrow at work :)
Dim i As Long
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields(" AM")
For i = 1 To .PivotItems.Count
If .PivotItems(i) = s1.Range("D2").Value Then
Dim SPgField1 As Variant
s1.Range("D2").Select
SPgField1 = Selection
.CurrentPage = SPgField1
End If
Next i
End With
End Su
-------------------


The second part of my quest to make it dynamicly was to loop throug
all the values on Sheet("Inhoud") where I manage the default values.
think this should do the trick, but just as above, I haven't tested i
yet!


Code
-------------------
Function SetPivotFieldFunction(WS, PT, PF, OptionX) As Boolean
'NOT TESTED YET... will test tomorrow at work :)
Dim i As Long
With WS.PT.PF
For i = 1 To .PivotItems.Count
If .PivotItems(i) = OptionX Then
Dim SPgField1 As Variant
SPgField1 = OptionX
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
SetPivotFieldFunction True
Else
SetPivotFieldFunction False
End If
Next i
End With
End Functio
-------------------



Code
-------------------
Sub SetPivotField()
'NOT TESTED YET... will test tomorrow at work :)
Dim i As Long
Dim WS As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim OptionX As String

For i = 1 To 10
WS = s1.Range("A" & i)
PT = s1.Range("B" & i)
PF = s1.Range("C" & i)
OptionX = s1.Range("D" & i)

If SetPivotFfieldFunction(WS, PT, PF, OptionX) = True Then
MsgBox "True... " & PF & "." & OptionX, vbInformation
Else
MsgBox "False... " & PF & "." & OptionX, vbCritical
End If
Next i
End Su
-------------------


If you see anything that should be changed before I run my tes
tomorrow, please let me know in advance

--
s80N

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

Almost there... dynamic and conditional set of PivotItems
 

Dim i As Long
With WS.PT.PF
For i = 1 To .PivotItems.Count
If .PivotItems(i) = OptionX Then
Dim SPgField1 As Variant '!!!!!Move out of Loop!!!!!
SPgField1 = OptionX
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
SetPivotFieldFunction True
Else
SetPivotFieldFunction False
End If
Next i
End With
End Function

Dim statements should ALWAYS be at the begining of code.

Die_Another_Day
s80NL wrote:
Absolutely awesome :cool: I've created to following code out of your
remarks, but I've to wait till tomorrow morning to test it at work...
so I'll let you know the results later.

How silly can it be Value instead of Select... Today, I've been staring
at that stupid line for ages :eek:


Code:
--------------------
Sub ShouldWorkNow()
' NOT TESTED YET... will test it tomorrow at work :)
Dim i As Long
With Sheets("Aflopende contracten").PivotTables("PivotAFL").PivotFields(" AM")
For i = 1 To .PivotItems.Count
If .PivotItems(i) = s1.Range("D2").Value Then
Dim SPgField1 As Variant
s1.Range("D2").Select
SPgField1 = Selection
.CurrentPage = SPgField1
End If
Next i
End With
End Sub
--------------------


The second part of my quest to make it dynamicly was to loop through
all the values on Sheet("Inhoud") where I manage the default values. I
think this should do the trick, but just as above, I haven't tested it
yet!


Code:
--------------------
Function SetPivotFieldFunction(WS, PT, PF, OptionX) As Boolean
'NOT TESTED YET... will test tomorrow at work :)
Dim i As Long
With WS.PT.PF
For i = 1 To .PivotItems.Count
If .PivotItems(i) = OptionX Then
Dim SPgField1 As Variant
SPgField1 = OptionX
.CurrentPage = SPgField1
MsgBox ("OK / i=" & i)
SetPivotFieldFunction True
Else
SetPivotFieldFunction False
End If
Next i
End With
End Function
--------------------



Code:
--------------------
Sub SetPivotField()
'NOT TESTED YET... will test tomorrow at work :)
Dim i As Long
Dim WS As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim OptionX As String

For i = 1 To 10
WS = s1.Range("A" & i)
PT = s1.Range("B" & i)
PF = s1.Range("C" & i)
OptionX = s1.Range("D" & i)

If SetPivotFfieldFunction(WS, PT, PF, OptionX) = True Then
MsgBox "True... " & PF & "." & OptionX, vbInformation
Else
MsgBox "False... " & PF & "." & OptionX, vbCritical
End If
Next i
End Sub
--------------------


If you see anything that should be changed before I run my test
tomorrow, please let me know in advance.


--
s80NL


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