Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to cycle through displayed PivotItems instead of the VisibleItemscollection
Hi,
I've run into a bit of a pickle with a VBA script. The script sets formatting on PivotItems by cycling through the VisibleItems collection. This works fine until I add a page field. I've figured out that the Visible property of the PivotItem stays True even though the item may be hidden by the selection on the page field. The collection is therefore not that useful to me since it will cause errors with any calls to get a range for any PivotItem hidden from view by the page field. How can I get around this? Below is an excerpt of the script in question: ================================================== 01 Dim pvtItems As PivotItems, pvtItem As PivotItem 02 Set pvtItems = pvtTable.PivotFields(myRow).VisibleItems 03 For Each pvtItem In pvtItems 04 pvtItem.LabelRange.Select 05 06 With Selection 07 .Borders.LineStyle = xlNone 08 .Borders(xlEdgeTop).LineStyle = xlContinuous 09 .Borders(xlEdgeTop).Weight = xlHairline 10 .Borders(xlEdgeTop).ColorIndex = xlAutomatic 11 .Borders(xlEdgeBottom).LineStyle = xlContinuous 12 .Borders(xlEdgeBottom).Weight = xlHairline 13 .Borders(xlEdgeBottom).ColorIndex = xlAutomatic 14 End With 15 Next pvtItem ================================================== The above script will fail on line 04 as soon as it hits an PivotItem which is hidden from view by any other selections made in the pivot table (e.g. on the page field). Your help is greatly appreciated. - Thomas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to cycle through displayed PivotItems instead of theVisibleItems collection
On Apr 22, 11:49*am, Thomas V wrote:
Hi, I've run into a bit of a pickle with a VBA script. The script sets formatting on PivotItems by cycling through the VisibleItems collection. This works fine until I add a page field. I've figured out that the Visible property of the PivotItem stays True even though the item may be hidden by the selection on the page field. The collection is therefore not that useful to me since it will cause errors with any calls to get a range for any PivotItem hidden from view by the page field. How can I get around this? Below is an excerpt of the script in question: ================================================== 01 * * *Dim pvtItems As PivotItems, pvtItem As PivotItem 02 * * *Set pvtItems = pvtTable.PivotFields(myRow).VisibleItems 03 * * *For Each pvtItem In pvtItems 04 * * * * * * *pvtItem.LabelRange.Select 05 06 * * * * * * *With Selection 07 * * * * * * * * * * *.Borders.LineStyle = xlNone 08 * * * * * * * * * * *.Borders(xlEdgeTop).LineStyle = xlContinuous 09 * * * * * * * * * * *.Borders(xlEdgeTop).Weight = xlHairline 10 * * * * * * * * * * *.Borders(xlEdgeTop).ColorIndex = xlAutomatic 11 * * * * * * * * * * *.Borders(xlEdgeBottom).LineStyle = xlContinuous 12 * * * * * * * * * * *.Borders(xlEdgeBottom).Weight = xlHairline 13 * * * * * * * * * * *.Borders(xlEdgeBottom).ColorIndex = xlAutomatic 14 * * * * * * *End With 15 * * *Next pvtItem ================================================== The above script will fail on line 04 as soon as it hits an PivotItem which is hidden from view by any other selections made in the pivot table (e.g. on the page field). Your help is greatly appreciated. - Thomas Hi again, I've found how to work around the issue. Since the routine causes predictable errors one can simply trap the errors and work from there. Here's an updated script: 01 Dim pvtItems As PivotItems, pvtItem As PivotItem 02 Set pvtItems = pvtTable.PivotFields(myRow).VisibleItems 03 For Each pvtItem In pvtItems 04 'Set error handling as we want it 05 Err.Clear 06 On Error Resume Next 07 pvtItem.LabelRange.Select 'this is the line we trap errors for 08 If Err.Number = 0 Then 09 With Selection 10 .Borders.LineStyle = xlNone 11 .Borders(xlEdgeTop).LineStyle = xlContinuous 12 .Borders(xlEdgeTop).Weight = xlHairline 13 .Borders(xlEdgeTop).ColorIndex = xlAutomatic 14 .Borders(xlEdgeBottom).LineStyle = xlContinuous 15 .Borders(xlEdgeBottom).Weight = xlHairline 16 .Borders(xlEdgeBottom).ColorIndex = xlAutomatic 17 End With 18 End If 19 'Turn on regular error handling again 20 Err.Clear 21 On Error GoTo MyErrorHandler 22 Next pvtItem I hope this is useful to others. - Thomas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to cycle through displayed PivotItems instead of theVisibleItems collection
I stumbled over a similar problem recently while trying to save specific pivot table layouts. I solved it by using a case select and making the pagefield temporarily a rowfield. .... Dim pt as PivotTable Dim objPF As PivotField Dim pfItem As PivotItem .... For Each objPF In pt.VisibleFields Select Case objPF.Orientation Case xlRowField For Each pfItem In objPF.PivotItems If pfItem.Visible = True Then ... End If Next pfItem Case xlColumnField For Each pfItem In objPF.PivotItems If pfItem.Visible = True Then ... End If Next pfItem Case xlPageField objPF.Orientation = xlRowField For Each pfItem In objPF.PivotItems ' Debug.Print pfItem.Parent.Name, pfItem.Name, pfItem.Visible If pfItem.Visible = True Then .... End If Next pfItem objPF.Orientation = xlPageField Case xlDataField Case xlHidden End Select Next objPF |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a graph with 2-cycle X 3-cycle log-log graph paper? | Charts and Charting in Excel | |||
Missing PivotItems | Excel Programming | |||
PivotItems Bug?! | Excel Programming | |||
How do I keep result from 1 iteration cycle to use in next cycle? | Excel Discussion (Misc queries) | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |