*Resolved* Pivot Stumper - Can't show Field Lists

[Greg Truby]

I don't know how I done it. But I have managed to totally goober up my pivot table settings at a global level.

Start with a data worksheet and do Data | PivotTable and Pivot Chart Report... and just click the Finish button and you get a skeleton with the grey "Drop Column Fields Here", "Drop Data Items Here", "...Row...", "...Page..." and blue highlights around each. I don't know when -- but it must be recently, perhaps even this morning... I did something that turned off this behavior. Now I don't see the grey messages. Now I don't see the blue outline. Normally, if that were the case, one simple clicks on the Show Field List buttons and voilá. But neither on existing pivots in workbooks that have always been well-behaved and new pivots too, I cannot get the Show Field List buttons to work! Neither the default feller on the PT toolbar, nor the same button on the popup menu that you get when right-click the PT. Even more amusing? The button(s) are not disabled. If I click somewhere off the PT, then yes, the buttons disable. Click back on the PT and the button on the PT toolbar "enables". They just don't do anything.

Troubleshooting failures so far...

Under Tools | Options... the View tab: Show All on Object is selected. ~ on Edit tab the Allow cell drag and drop is checked.

I'm trying to peruse the object browser for properties that might impact this behavior. So far I've looked at <ul>[*]Workbook.ShowPivotTableFieldList (I had high hopes for that one)[*]PivotTable.EnableFieldList[*]PivotTable.EnableFieldDialog [*]PivotField.DragToColumn[*]PivotField.DragToData[*]PivotField.DragToRow[/list]The following code return TRUE's across the board...

Sub CheckFieldList()

    Dim pvtTable As PivotTable, pfX As PivotField, strDragProps, strEnableds

    Set pvtTable = ActiveSheet.PivotTables(1)

    ' Determine if field list can be displayed.
    
    With pvtTable
        strEnableds = "Field List: " & vbTab & .EnableFieldList & vbCr & _
                      "Field Dialog:" & vbTab & .EnableFieldDialog
    End With
    
    MsgBox strEnableds, vbInformation, pvtTable.Name
    On Error GoTo ErrorHandler
    For Each pfX In pvtTable.PivotFields
        With pfX
            
            strDragProps = strDragProps & .Name & vbTab & _
                                        "Drag2Col: " & .DragToColumn & vbTab & _
                                        "Drag2Data: " & .DragToData & vbTab & _
                                        "Drag2Row: " & .DragToRow & vbCr

        End With
    Next pfX
    
    MsgBox strDragProps, vbInformation, pvtTable.Name & " - Field Drag Properties"

    Exit Sub
    
ErrorHandler:
'"""""""""""
    strDragProps = strDragProps & pfX.Name & " «errors» " & vbCr
    Resume Next
End Sub

Sub UseShowPivotTableFieldList()

    Dim wkbOne As Workbook

    Set wkbOne = Application.ActiveWorkbook

    'Determine PivotTable field list setting.
    If wkbOne.ShowPivotTableFieldList = True Then
        MsgBox "The PivotTable field list can be viewed."
    Else
        MsgBox "The PivotTable field list cannot be viewed."
    End If

End Sub

Other failed tests:<ul>[*]Shutting down Excel[*]Rebooting[*]Toggle Events Off/On[*]Going into Table Options... for a PT and unchecking all options and then one-by-one re-checking them[*]Immediate Window: ActiveSheet.PivotTables(1).EnableWizard = true/false[/list]Code that builds PT's still works okay. But the only way I can now build a pivot interactively is to the wizard and click the layout button and drag fields inside the dialog box in the wizard.

Searched here and w/ Google and ain't seen anyone else w/ this prob so far.

So, I'm plum stumped. If'n anybody kin figure out what the heck I done, I'll be much obliged!

 

[Gettingbetter]

Hi Greg,

Sorry no answer from me,

Just to let you know when looking for an answer for you, I found someone who shared your problem.

http://www.dbforums.com/showthread.php?p=6243356#post6243356

So your not alone (not that it helps), looks like they were incorrectly pointed in the direction of .ShowPivotTableFieldList but you have already tried this.

I have used another program (can't remeber its name, but not microsoft) and im not sure but maybe in access, there is something you can do to a form that removes it from design mode, so that pivot tables can't be changed. you then have to put it in design mode to change them (a bit like the Control Toolbox items in excel). This made me think about queries (Im sure I once ended up in an excel style design mode after messing one of those up) maybe you have done something similar (created any unuasual queries lately.

Just thought I would try and help (you have helped me loads)

Cheers
GB

 

[Tom Urtis]

Greg - -

Just for the heck of it, try something. Make a copy of your workbook and delete any pivot table(s) that had been created from the source data of interest. Close all other workbooks.

Next, activate the worksheet with the source data.

Next, go to the VBE, and paste this into the Immediate Window and press Enter:

ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion).CreatePivotTable TableDestination:="R3C" & Range("A1").CurrentRegion.Columns.Count + 2


Go back to your active worksheet and a new pivot table will be created on that sheet, whose source is active sheet current region with destination as row 3 of current region columns + 2. If your source data does not start in cell A1, please modify the above codeline with the proper top left corner cell address.

Note, this won't look like a pivot table, it will look like 4 cells with thin borders around them. However, select one of those cells and you will see that it really is an empty pivot table, ready for you to drag row, column, data, and page fields into it from the Field List that should pop up when you select any of those 4 cells assuming, as I'm sure you have made sure, that the Show Field List option has been selected.

Any luck?

 

[starl]

You know.. after reading Tom's solution, this problem is sounding familiar...

 

[Greg Truby]

Tom,

I get the four cells with a border all right, but when I click inside them it's dead. Again, PT toolbar's Show Field List button does nothing. Nor does the same button on the r-click's popup, even though both are "enabled". Also tried doing a Pivot Chart and making sure the Hide Pivot Buttons option that appears on P-Charts was not defaulting to checked and it doesn't. I get the blue boxes on a p-chart, but cannot turn on the field list there either.

 

[Greg Truby]

Tracy,

Renamed C:\PF\MO\Off11\xl5en32.olb and Excel rebuilt. But didn't help.

As for extended monitor, I thought of that -- but then wouldn't the button still show "activated" (like the "bold" or "italic" buttons do when active) and the PT skeleton still change to blue-outlined even if the list were off-screen?

I'll try shutting down, disconnecting my 2nd monitor and re-booting to see. But it seems a long shot (but I'm will to try 'bout anything at this point).

 

[Tom Urtis]

I'm just guessing here because I have not experienced before what you are seeing, but if you save and close that workbook, close Excel, reopen Excel, re-open the workbook, select any cell in the data section of the pivot table, and in the Immediate Window execute

Err.Clear

and

ActiveWorkbook.ShowPivotTableFieldList = True

does this do any good?


I am not trying to sound funny, but have you looked really really closely at the worksheet to see if maybe, just maybe, the field list actually is visible, if only because its title bar is barely peeking above the status bar or task bar?


The final suggestion, which I hate doing, is to reload Excel, but it would be interesting to find out why this is happening instead of resorting to that.

 

[starl]

hmm - try xlb... I always screw that up... sorry

 

[starl]

instead of a reload, how about a repair??

 

[ChrisM]

This property sounds promising, but didn't do anything for me (of course my XL is not broken!)

Application.GenerateGetPivotData = True/False

Just curious, you are trying all kinds of pivot table codes, but are you sure something is not grabbing/overriding your keypresses? Could any worksheet/application/add-in events be hijacking your clicks?