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...
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!
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!