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

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,030
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...
Code:
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!
 
This is a stab in the dark and something I just tried. If a sheet is protected and you don't have Select locked cells and Select unlocked cells checked, the pivot table displays behavior like what you describe.
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Tom,

Err.Clear & AWB.SPTFL = T no workie either :-(

<hr />

Chris,

The GenerateGetPivotData is a different kettle of fish. FWIW, I have customized my PT Toolbar to have that button on the tbar. But that toggles a different property.

<hr />

Paul (parmel),

Isn't protection. If I turn on sheet protection on the WS w/ the PT, then the PT wizard option becomes disabled as does the Show Field List button

<hr />

Tracy & Tom,

I did reboot with just one monitor. No, it's not hiding off the screen or at the edge of the screen. I went over to a co-workers desk that's out today and fired up her Excel. No probs. Plus, I was right, that button behaves like the Bold & Italic buttons, i.e. its status reflects the toggled status of the property. If I clicked on her machine, it "stuck" when the list is being shown. Here's what I don't get, if I do toggle the ShowPivotTableFieldList in the immediate window, it toggles the button on the toolbar on her machine. However on my own, ? activeworkbook.showpivottablefieldlist always returns a TRUE, even though it is not true. I, even went so far as to unhide PERSONAL.XLS and build a pivot there; thinking maybe it was somehow stuck in a hidden WB. But nope, still queered up.

Gonna try nuking the XLB file next.
 
Upvote 0
xlb would be my next attempt also at this point.

Good luck, I'd be interested to know how it turns out.
 
Upvote 0
Well,

<h3>Hats off to Miss Tracy!!!</h3> :bow:<sup>10</sup>

Seems a corrupted XLB file *can* cause this! Holy smokes, but that's a whopper of a disconnect.

So, Mrs. Syrstad, (if'n yer hubby don't object too much) here's a *virtual hug* from a very thankful feller down ta KC.

Much obliged to everyone that looked this one over and thought a spell on it.
 
Upvote 0
I like hugs :-)

That xlb file stores all kinds of settings. I don't know if yours was corrupt (tho I read an article that says they corrupt easily and you can tell because of size - they should only be a few kb), but mine once was (my Calculation was stuck on Manual) - corrupt and getting big.

Glad it worked. Hope you didn't lose too much customization.
 
Upvote 0
...Hope you didn't lose too much customization.
~starl

Actually, when I had to make the jump from one notebook to another two generations ago I did lose oodles and oodles of customizations. The time after that was when I learned about xlb files and the time before last it was better. But then I did have an XLB file corrupt and again lost oodles of customizations.

Since that time I have changed how I do my toolbars. I use UserForms to store all of my customizations and I kill & rebuild all my toolbars. I am in the process of doing my Userform as Class Module thing and creating an unholy crossbreed between the two that will make creating custom toolbars where the user can customize the installation of the toolbar and any menu addtions extremely easy to code.

So, no, didn't lose too much! :-D
 
Upvote 0

Forum statistics

Threads
1,224,862
Messages
6,181,458
Members
453,042
Latest member
AbdelrahmanExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top