Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Almost there... dynamic and conditional set of PivotItems
Absolutely awesome 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controlling Pivotitems.visible | Excel Discussion (Misc queries) | |||
Adding PivotItems | Excel Programming | |||
Always keep the same 4 PivotItems visible | Excel Programming | |||
pivotitems in a multiuser environment | Excel Programming | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |