Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to access visible property of Pivot Items class
Hi,
with the following code I get run-time error 1004 with the above description: Set pt = ActiveSheet.PivotTables("Pivot_Table") Set pf = pt.PivotFields("Pivot Class") With pf .Orientation = xlColumnField .Position = 1 For Each pi In pf.PivotItems If pi.Name = "#N/A" Or "1" Then pi.Visible = False Else: pi.Visible = True End If Next pi End With The error occurs at the line pi.Visible = False funnily enough when the test condition of the If statement has failed, i.e. execution should have skipped this line. Also, the Orientation property of the PivotField for some reason changes to xlHidden, after it has been explicitly set to xlColumnField before entering the If statement. I have a suspicion that this may be behind it, but I don't know of a way to stop this particular property changing. Any suggestions? Thanks -- There are 10 types of people in the world - those who understand binary and those who don't. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to access visible property of Pivot Items class
To prevent the error, set the Sort for the field to Manual. For example:
With pf .AutoSort xlManual, "Pivot Class" .Orientation = xlColumnField Geoff wrote: Hi, with the following code I get run-time error 1004 with the above description: Set pt = ActiveSheet.PivotTables("Pivot_Table") Set pf = pt.PivotFields("Pivot Class") With pf .Orientation = xlColumnField .Position = 1 For Each pi In pf.PivotItems If pi.Name = "#N/A" Or "1" Then pi.Visible = False Else: pi.Visible = True End If Next pi End With The error occurs at the line pi.Visible = False funnily enough when the test condition of the If statement has failed, i.e. execution should have skipped this line. Also, the Orientation property of the PivotField for some reason changes to xlHidden, after it has been explicitly set to xlColumnField before entering the If statement. I have a suspicion that this may be behind it, but I don't know of a way to stop this particular property changing. Any suggestions? Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to access visible property of Pivot Items class
Thanks very much Debra, this was exactly the solution needed. This of course
then helped me to discover the glaring error in my condition, where I asked Excel to check whether "1" was true, rather than checking pi.Name = "1" :) Thanks "Debra Dalgleish" wrote: To prevent the error, set the Sort for the field to Manual. For example: With pf .AutoSort xlManual, "Pivot Class" .Orientation = xlColumnField Geoff wrote: Hi, with the following code I get run-time error 1004 with the above description: Set pt = ActiveSheet.PivotTables("Pivot_Table") Set pf = pt.PivotFields("Pivot Class") With pf .Orientation = xlColumnField .Position = 1 For Each pi In pf.PivotItems If pi.Name = "#N/A" Or "1" Then pi.Visible = False Else: pi.Visible = True End If Next pi End With The error occurs at the line pi.Visible = False funnily enough when the test condition of the If statement has failed, i.e. execution should have skipped this line. Also, the Orientation property of the PivotField for some reason changes to xlHidden, after it has been explicitly set to xlColumnField before entering the If statement. I have a suspicion that this may be behind it, but I don't know of a way to stop this particular property changing. Any suggestions? Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to access visible property of Pivot Items class | Excel Programming | |||
Unable to set the visible property of the PivotItem class | Excel Programming | |||
"Unable to get Pivot Tables Property of Worksheet Class "Error | Excel Programming | |||
Error:Unable to set the visible property of the PivotItem class. | Excel Programming | |||
Run-time error '1004' - Unable to set the Visible property of the Worksheet class | Excel Programming |