Determine if a PivotField is visible?

[Whitney Yiu]

I am currently working with a Pivot Table. What i am trying to do is
determine if a Pivot Field is visible or not, and if it is, then i want to
make it hidden. To set a Pivot Field to hidden, i use the following code:

Set pf = pfs.Item("Sum of Actual")
MsgBox pf.Name
pf.Orientation = xlHidden

However, this appears to break if the Pivot Field "Sum fo Actual" is already
hidden. I get "Run-time error '1004': Unable to get the Item property of the
PivotFields class" error on the line that tries to set pf to the "Sum of
Actual" pivot field.

Does anyone have any ideas on what i can do to fix this?

Any help is GREATLY appreciated! Thanks

Whitney

 

[Bill Renaud]

I set up a short macro and pivot table to try this out. I am assuming that
"Sum of Actual" means that the original column of data was labeled
"Actual", and that it has been placed as a Data Field (to sum up the
values). Making it hidden will essentially remove it from the data area of
the pivot table.

Public Sub Test()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pfSumOfActual As PivotField

On Error Resume Next

Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Set pfSumOfActual = pt.PivotFields("Sum of Actual")

If pfSumOfActual Is Nothing _
Then
'Field is Hidden (not currently in the pivot table).
Else
'Field is currently visible.
pfSumOfActual.Orientation = xlHidden
End If
End Sub

 

[Whitney Yiu]

Thank you for your help Bill. Your assumptions were correct.

I used your code, and i tried it. The first thing i did was make sure that i
manually added "Actual" as a Data Field, so that it became "Sum of Actual" in
the pivot table. Then, i ran your code, and it worked just fine. The "Sum of
Actual" column was hidden from view in the pivot table.

Then, i ran your code again (i added a line to display a message box if
pfSumOfActual is nothing), hoping that i would get a message box, but
instead, the code broke on the "Set pfSumOfActual = pt.PivotFields("Sum of
Actual")" line.

I got the error that was in my original post. I think i understand why this
error pops up (i'm guessing that if the field is hidden, it's not counted as
a pivot field anymore), but is there another way to get the desired
functionality?

Thank you for your help!

Whitney

 

[Bill Renaud]

<<Then, i ran your code again (i added a line to display a message box if
pfSumOfActual is nothing), hoping that i would get a message box, but
instead, the code broke on the "Set pfSumOfActual = pt.PivotFields("Sum of
Actual")" line.>>

With an error handler in place, this line should simply set pfSumOfActual
to Nothing, so there should be no error.

What was the exact error? Run-time error 1004 or other?

 

[Bill Renaud]

<<Then, i ran your code again (i added a line to display a message box if
pfSumOfActual is nothing), hoping that i would get a message box, but
instead, the code broke on the "Set pfSumOfActual = pt.PivotFields("Sum of
Actual")" line.>>

You must have the option "Break on All Errors" set on the General tab of
the Tools|Options dialog box in the VBA editor.
Try setting it to "Break in Class Module.

Sorry I didn't think about this a little more before posting the reply
right above this one.

 

[Whitney Yiu]

Can you give an example of what you mean by an error handler?

It was a run-time 1004 error.

Thanks.

 

[Bill Renaud]

<<Can you give an example of what you mean by an error handler?>>

------------------------------------
Example #1:

On Error Resume Next

....means continue with the next statement, even if there is an error (leave
the error checking of object variables up to the programmer.)

------------------------------------
Example #2:

Sub Test()

On Error Goto ErrHandler

'more code here
Exit Sub

ErrHandler:
MsgBox "This is an error."
End Sub

 

[Bill Renaud]

<<With an error handler in place, this line should simply set pfSumOfActual
to Nothing, so there should be no error.>>

Actually, this is not quite correct. An error still occurs, but because of
the statement further up in the routine:
On Error Resume Next

.... the program simply continues on anyway (assuming that the "Break in
Class Module" option is set for error handling), so that the programmer can
catch it by manually checking anything that might have caused an error (the
"If pfSumOfActual Is Nothing ..." line in my code).

See http://www.cpearson.com/excel/ErrorHandling.htm for a whole page of
examples and more explanation.