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
[*]Workbook.ShowPivotTableFieldList (I had high hopes for that
following code return TRUE's across the board...
Dim pvtTable As PivotTable, pfX As PivotField, strDragProps, strEnableds
Set pvtTable = ActiveSheet.PivotTables(1)
' Determine if field list can be displayed.
strEnableds = "Field List: " & vbTab & .EnableFieldList & vbCr & _
"Field Dialog:" & vbTab & .EnableFieldDialog
MsgBox strEnableds, vbInformation, pvtTable.Name
On Error GoTo ErrorHandler
For Each pfX In pvtTable.PivotFields
strDragProps = strDragProps & .Name & vbTab & _
"Drag2Col: " & .DragToColumn & vbTab & _
"Drag2Data: " & .DragToData & vbTab & _
"Drag2Row: " & .DragToRow & vbCr
MsgBox strDragProps, vbInformation, pvtTable.Name & " - Field Drag Properties"
strDragProps = strDragProps & pfX.Name & " «errors» " & vbCr
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."
MsgBox "The PivotTable field list cannot be viewed."
Other failed tests:[*]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!