to deny items of context menu for PivotTable

[vics_roo]

Hi,

I have PivotTable. I need to deny for user the part of context menu (by mouse right click)
Need to deny these items
"Show field list"
"Show Values As"

How I can remove them or set unactive ?

Thanks at advance
Vic

 

[vics_roo]

I did something like that

Option Explicit

Public Sub New_Commandbar()
Dim Cbr As CommandBar
Dim Ctr As CommandBarControl
On Error Resume Next
Application.CommandBars("PivotTable Context Menu").Delete
Application.CommandBars.Add Name:="PivotTable Context Menu", Position:=msoBarPopup, Temporary:=True
For Each Ctr In Application.CommandBars("PivotTable Context Menu").Controls
    With Application.CommandBars("PivotTable Context Menu").Controls.Add(Ctr.Type, Ctr.ID, Ctr.Parameter, , 1)
        If (Ctr.Caption <> "Show field list") Or (Ctr.Caption <> "Show Values As...") Then
        
        Else
            Ctr.Delete
        End If
    End With
Next
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  New_Commandbar
  Application.CommandBars("PivotTable Context Menu").Show
End Sub

Seems it works...but i have a lot of empty items in context menu...and OOOPS it's affect not only Active workbook,but all workbooks with Pivot....

How to remove empty items ? and do it only for active workbook ?

 

[vics_roo]

I fixed some bugs. Now my code is

Option Explicit

Dim ContextMenu As CommandBar

Public Sub New_Commandbar()
'    Dim Cbr As CommandBar
    Dim Ctr As CommandBarControl
    On Error Resume Next

    
    Application.CommandBars("PivotTable Context Menu").Delete
    Application.CommandBars.Add Name:="PivotTable Context Menu", Position:=msoBarPopup, Temporary:=True
    For Each Ctr In Application.CommandBars("PivotTable Context Menu").Controls
        With Application.CommandBars("PivotTable Context Menu").Controls.Add(Ctr.Type, Ctr.ID, Ctr.Parameter, , 1)
          If (Ctr.Caption <> "Show field list") Or (Ctr.Caption <> "Show Values As...") Then
        
          Else
                Ctr.Delete
          End If
        End With
    Next
    Set ContextMenu = Application.CommandBars("PivotTable Context Menu")
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("PivotTable Context Menu").Reset
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  New_Commandbar
  Application.CommandBars("PivotTable Context Menu").Show  ' <-COMPILE ERROR method or data member not found
End Sub

But I have compile error.So how to remove these items from pivot table context menu ?

Thanks