CODEWITHSUNDEEP
excelvbacontextmenu
Ibo
Ibo

Reputation: 4319

Delete context menu options invoked from inside the context menu itself

I am trying to delete some custom command options in the context menu of Excel. I have assigned the following sub to one of the context menu buttons to do the job, however, when it gets to delete this button itself, it cannot do that. How can I achieve this, or is there a workaround such as releasing the control to another sub, etc?

Sub DeleteFromRightClickMenuOptions(sRightClickMenu As String)
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl

    ' Set ContextMenu to the Cell context menu.
    Set ContextMenu = Application.CommandBars(sRightClickMenu) 'instead of "List Range Popup", "Cell" can be used for regulat cells

    ' Delete the custom controls with the Tag : My_Tag.
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "My_Tag" Then
            ctrl.Delete
        End If
    Next ctrl
End Sub

Upvotes: 0

Views: 244

Answers (1)

Ibo
Ibo

Reputation: 4319

Finally, I found an easy solution for it: Application.OnTime

The function that should be assigned to the delete command in the context menu should call another function that will run the main delete function one second later:

Sub DeleteFromRightClickMenuOptions_Main()
'since this sub is invoked by a context menu item and the item itself cannot be deleted we call another function and
'release the control this way
    On Error Resume Next
    Application.OnTime Now + TimeValue("00:00:01"), "RemoveContextMenuOptions"
End Sub

where RemoveContextMenuOptions is the function that calls DeleteFromRightClickMenuOptions with proper arguments

Upvotes: 1

Related Questions