Why can't I edit a commandbar control in Excel 2013 with VBA?

Question

I'm trying to make my own drill-through action for PivotTable by means of VBA. The action will be called from context menu of a PivotTable from Additional Actions. I want to put my button under Additional Actions control of PivotTable Context Menu command bar. The point is that by default Additional Actions already contains (No Actions Defined) item. So, I want to remove this (No Actions Defined) after adding my button, but nothing works. I cannot even change any property of (No Actions Defined) control, like Caption, Visible, etc. What might be the reason, and what is the workaround? Here is my code so far (you can put it under Workbook_SheetBeforeRightClick, for example, and then test with any Pivot Table in that workbook):

Dim PCell                   As PivotCell
Dim PComBar                 As CommandBar
Dim PControl                As CommandBarControl
Dim DControl                As CommandBarControl
Dim BControl                As CommandBarControl
Dim IsFromPivotTable        As Boolean

IsFromPivotTable = False
On Error GoTo NotFromPivot
Set PCell = Target.PivotCell
IsFromPivotTable = True
NotFromPivot:
On Error GoTo 0
If IsFromPivotTable Then
    Set PComBar = Application.CommandBars("PivotTable Context Menu")
    Set PControl = PComBar.Controls("Additional Actions")
    On Error Resume Next
    With PControl
        Call .Controls("My Drillthrough Action").Delete
        .Enabled = True
    End With
    On Error GoTo 0
    Set DControl = PControl.Controls.Add(Type:=msoControlButton, Temporary:=True, Before:=1)
    With DControl
        .Style = msoButtonIconAndCaption
        .Caption = "My Drillthrough Action"
        .FaceId = 786
    End With
    On Error Resume Next
        Set BControl = PControl.Controls("(No Actions Defined)")
        With BControl 'This does not work and throws error if do not suppress with On Error
            .Enabled = True
            .Visible = False
            .Caption = "Hello there"
        End With
    On Error GoTo 0
End If

So, the last section With BControl ... End With does not work at all, and throws an error "Automation Error". I can successfully edit Additional Actions itself, like enable it, but I would like to get rid of (No Actions Defined) control, or replace it with my own. Note, that Call .Controls("(No Actions Defined)").Delete does not work either. How can I do that? I tried to google the problem, but no luck...

Answer 1

I suspect you can't add to that menu. You could, however, add to the context menu itself:

Sub test()
Dim PCell As PivotCell
Dim PComBar As CommandBar
Dim DControl As CommandBarControl
Dim target As Excel.Range

    Set target = ActiveCell
    On Error Resume Next
    Set PCell = ActiveCell.PivotCell
    On Error GoTo 0
    If Not PCell Is Nothing Then
        Set PComBar = Application.CommandBars("PivotTable Context Menu")
        Set DControl = PComBar.Controls.Add(Type:=msoControlButton, Temporary:=True, Before:=1)
        With DControl
            .Style = msoButtonIconAndCaption
            .Caption = "My Drillthrough Action"
            .FaceId = 786
        End With
    End If
    End Sub