I am using Office 2000 and had a request from customer to
add ability to check/uncheck all items in a specified
pivot table field.
I found a code on the web that does that, but there is a
problem with it. when trying to check an item in the list
(pivot table field), by using this command "Items.Visible
= True", excel is giving error message, but "Items.Visible
= False" work fine.
Whith the help of the internet search, I found the
solution to this problem also. I had to change the "Sort"
option in the Advenced" properties of a field to Manual.
Then my code worked.

Here is my question...

Our fields have different sort order. Could you tell me
what code should I use in order to do the following:

1) Read and store existing sort property of a specified
field
2) Change the sort of the field to Manual
3) Do the check/uncheck (i have code for that)
4) Set sort property of the field back to original (that
was stored in step 1)

Any help is appreciated.

Thanks a lot

The following code will capture and reset the sort order for each field:
'========================================
Sub PivotShowItemResetSort()
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
'returns sort order to previous setting
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim intASO As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.VisibleFields
intASO = pf.AutoSortOrder
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible < True Then
pi.Visible = True
End If
Next pi
pf.AutoSort intASO, pf.SourceName
Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'========================================

Stan R wrote:
I am using Office 2000 and had a request from customer to
add ability to check/uncheck all items in a specified
pivot table field.
I found a code on the web that does that, but there is a
problem with it. when trying to check an item in the list
(pivot table field), by using this command "Items.Visible
= True", excel is giving error message, but "Items.Visible
= False" work fine.
Whith the help of the internet search, I found the
solution to this problem also. I had to change the "Sort"
option in the Advenced" properties of a field to Manual.
Then my code worked.

Here is my question...

Our fields have different sort order. Could you tell me
what code should I use in order to do the following:

1) Read and store existing sort property of a specified
field
2) Change the sort of the field to Manual
3) Do the check/uncheck (i have code for that)
4) Set sort property of the field back to original (that
was stored in step 1)

Any help is appreciated.

Thanks a lot


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html