Cannot see custom functions in Excel right-click menu

Question

Good morning experts.

My team created Excel Add-in toolbars to make it easier for end users to open our Excel macro tools. I recently noticed the custom functions in the code of these Add-in are no longer displaying (or assessable) on the Excel 'right-click' menu. We use Windows 10 / 64-bit MS Office 365/2016. I was wondering if anyone knows if these UDFs were disabled by Microsoft. I have no way to know if these are disabled by the security of our company. I have included the following code for context:

Public Sub AddItemToCellMenu()
'Application.CommandBars("Cell").Reset
Dim cmdBar As CommandBar, cmdPopup As CommandBarPopup, cmdButton As CommandBarButton

On Error GoTo ErrorHandler:

Set cmdBar = Application.CommandBars("Cell") '35
Set cmdPopup = cmdBar.Controls.Add(Type:=msoControlPopup, Before:=1)

With cmdPopup
   .Caption = "My Popup"
End With

Set cmdButton = cmdPopup.Controls.Add(Type:=msoControlButton)
With cmdButton
   .Caption = "This is my macro1"
   .OnAction = "Mymacro1"
   .FaceId = 370
End With

Set cmdButton = cmdPopup.Controls.Add(Type:=msoControlButton, Before:=1)
With cmdButton
   .Caption = "This is my macro2"
   .OnAction = "Mymacro2"
   .FaceId = 370
End With

Exit Sub

ErrorHandler:

Debug.Print Err.Number & " " & Err.Description

End Sub
Public Sub Mymacro1()
MsgBox "My macro 1"
End Sub
Public Sub Mymacro2()
MsgBox "My macro 2"
End Sub

Thank you in advance, VBA_Guy

I tried to execute the code above to no avail. I would expect more documentation online regarding this phenomenon.

Answer 1

• Your code changes the content menu for Normal and Page Layout. But it doesn't change the menu on Page Break Preview.

• Both content menu name are Cell.

• Tested on M365

Please try.

Public Sub AddItemToCellMenu()
    Dim cmdBar As CommandBar, cmdPopup As CommandBarPopup, cmdButton As CommandBarButton
    On Error GoTo ErrorHandler:
    For Each cmdBar In Application.CommandBars
        If cmdBar.Name = "Cell" Then
            cmdBar.Reset
            Set cmdPopup = cmdBar.Controls.Add(Type:=msoControlPopup, Before:=1)
            With cmdPopup
                .Caption = "My Popup"
            End With
            Set cmdButton = cmdPopup.Controls.Add(Type:=msoControlButton)
            With cmdButton
                .Caption = "This is my macro1"
                .OnAction = "Mymacro1"
                .FaceId = 370
            End With
            Set cmdButton = cmdPopup.Controls.Add(Type:=msoControlButton, Before:=1)
            With cmdButton
                .Caption = "This is my macro2"
                .OnAction = "Mymacro2"
                .FaceId = 370
            End With
        End If
    Next
    Exit Sub
ErrorHandler:
    Debug.Print Err.Number & " " & Err.Description
End Sub