Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
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 -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
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" wrote: 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 -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
<<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? -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
<<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. -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
Can you give an example of what you mean by an error handler?
It was a run-time 1004 error. Thanks. "Bill Renaud" wrote: <<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? -- Regards, Bill Renaud |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
<<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 ------------------------------------ See the Help topic "On Error Statement" in Visual Basic Help for more examples. -- Regards, Bill Renaud |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if a PivotField is visible?
<<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. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ShowDetail on PivotField rather than PivotItem?? | Excel Programming | |||
PivotField refreshment | Excel Programming | |||
Determine upper visible corner of sheet | Excel Programming | |||
PivotField select all | Excel Programming | |||
Programmatically determine if a control is .VISIBLE or not. | Excel Programming |