How to disable manual editing in Pivot Tables

[batyr]

Hello,

Today suddenly I was able to manually edit pivot tables without going to the source table. Please see attached pictures. In the pivot table before my manual editing James was showing as the source table. When I manually changed James to Ric in the pivot table (not the source table), it actually changed all instances of James to Ric in the pivot table, but the source table still shows James. When I clicked to update the pivot table, it still showed Ric instead of James, even though the source table was showing James. Moreover, when I changed James in the source table to another name, the new name appeared in the pivot table, but when I changed that new name back to James in the source table, the pivot table showed Ric after the update, not James. This is a bit of mystery to me. Could you please help me to disable that function. I wonder if it's a new thing in pivot tables? My excel is 365. Thanks

 

[xlhelp15]

Hi Welcome to MrExcel - The below one restricts the PivotWizard and when you right-click post running the code - PivotTable Options is on disabled mode. You can fit this in your code,

Sub PivotTWizardDisable()
Dim pt As PivotTable
    
On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo errHandler

If Not pt Is Nothing Then
   pt.EnableWizard = False
Else
   MsgBox "Please select a cell in a pivot table"
End If

exitHandler:
  Set pt = Nothing
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub