Context Menu Events?

[Remsai]

Hello,

Is it possible to capture a context(right click) menu event?

Like to show a msgbox when you click delete on the cell context menu.

I've searched everywhere online, but I can't seem to find anything.

I know that I can make a custom context menu, however that is not what I want.

I found "CommandBars.OnUpdate Event (Office)" and how to capture command bar events.

Thanks

 

[mikerickson]

As a test, I put this in a normal module. (Excel 2016)

Sub test()
    With Application.CommandBars("Cell").Controls(1)
        .OnAction = "Trial"
    End With
End Sub

Sub Trial()
    MsgBox "x"
End Sub

I ran test. Then right clicked on a cell, chose "cut" from the pop-up menu, and the msgbox appeared.

It looks like it works for 2016.

Edit: BIG WARNING!

This is an application level process that persists. After the above, I closed Excel (didn't save my work) and when I re-opened Excel and was looking at a new workbook, right clicked on a cell and was given an error message that Excel couldn't find the macro Trial. I had to run

Application.CommandBars("Cell").Controls(1).OnAction = ""

to get things back to normal.

In practice, it would probably be safer to replace the CommandBarControl object Application.CommandBars("cell").Controls(1) with my own control, that had its Temporary argument set to True.

 

[Jaafar Tribak]

As a test, I put this in a normal module. (Excel 2016)

Sub test()
    With Application.CommandBars("Cell").Controls(1)
        .OnAction = "Trial"
    End With
End Sub

Sub Trial()
    MsgBox "x"
End Sub

I ran test. Then right clicked on a cell, chose "cut" from the pop-up menu, and the msgbox appeared.

It looks like it works for 2016.

Edit: BIG WARNING!

This is an application level process that persists. After the above, I closed Excel (didn't save my work) and when I re-opened Excel and was looking at a new workbook, right clicked on a cell and was given an error message that Excel couldn't find the macro Trial. I had to run

Application.CommandBars("Cell").Controls(1).OnAction = ""

to get things back to normal.

In practice, it would probably be safer to replace the CommandBarControl object Application.CommandBars("cell").Controls(1) with my own control, that had its Temporary argument set to True.

I stand corrected.

I always thought setting the OnAction Property wouldn't work after the Ribbon was introduced ... What confused me further was when I tested :

Sub test()
    With Application.CommandBars.FindControl(, 294)
        .OnAction = "Trial"
    End With
End Sub


Sub Trial()
    MsgBox "x"
End Sub

Didn't work despite ID=294 corresponding to the Delete Menu but apparently not the one on the Cell commandbar .

This works : Application.CommandBars.FindControl(, 292).OnAction as well as Application.CommandBars("Cell").Controls(7).OnAction.

This is interesting and I see a lot of potential because with a bit of coding one could mimic the now missing _Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) event.

Thanks for the input.

 

[mikerickson]

Actualy, the Click event for the CommandBarButton still exists, i.e. the CommandBarButton object still exits (since context menus are CommandBars and the items in the pop-up menus are CommandBarButton objects)

My guess is that the ribbon is a new, separate thing, but to maintain backwards compatibility, MS kept the menus as they were, CommandBar objects.

 

[mikerickson]

Hello,

Is it possible to capture a context(right click) menu event?

Like to show a msgbox when you click delete on the cell context menu....

Capturing that is possible, but it is too risky for my taste.
That involves making changes to all workbooks and it persists until stopped. Too invasive for my taste.

What I prefer to do is add a temporary item to the menu that goes away when the workbook isn't active.

Put this in a normal module (of a disposable testing workbook)

' in normal module

Sub MyDeleteMacro()
    If MsgBox("Delete ?", vbYesNo) = vbYes Then
        Selection.Delete
    End If
End Sub

and this in the ThisWorkbook code module

' in ThisWorkbook code module

Private Sub Workbook_Activate()
    Call Workbook_Deactivate
    With Application.CommandBars("Cell")
        With .Controls.Add(Type:=msoControlButton, before:=1, Temporary:=True)
            .Caption = "My Delete"
            .OnAction = "MyDeleteMacro"
        End With
    End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call Workbook_Deactivate
End Sub

Private Sub Workbook_Deactivate()
    On Error Resume Next
    Do
        Application.CommandBars("Cell").Controls("MyDelete").Delete
    Loop Until Err
    On Error GoTo 0
End Sub

Private Sub Workbook_Open()
    Call Workbook_Activate
End Sub

Save that testing workbook, close it, and then re-open it.
Select some cells, right click and the MyDelete option should be in the menu.

 

[Jaafar Tribak]

Actualy, the Click event for the CommandBarButton still exists, i.e. the CommandBarButton object still exits (since context menus are CommandBars and the items in the pop-up menus are CommandBarButton objects)

My guess is that the ribbon is a new, separate thing, but to maintain backwards compatibility, MS kept the menus as they were, CommandBar objects.

I have never tested sinking the commandbarButton Click event for context menus... I assumed it woudn't work like it is the case with the commandbarbuttons located on the ribbon .

You have proved me wrong again.

I can't believe I have been all these years unaware of this !

Just to make sure because I am now getting paranoid about this - Can you try sinking the indent text click event - control (ID=3162) - in the same fashion and let me know what happens .

I know it won't work but just in case

Thanks.

 

[RoryA]

It’s worth remembering that there are two Cell menus (depending on which view you’re in) so you should alter both.

 

[Jaafar Tribak]

Just want to confirm the following:

Am i correct to say that capturing the _Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean) event for commandbarbuttons located on the ribbon (like "Iincrease Indent" button in the Home tab) is not possible ? ... BUT it is possible with controls in some context menus such as the Cell Menu like we have seen in this thread?

The same question for setting the OnAction Property.

Thanks

 

[Remsai]

Thank you for your contributions Jaafar, mikerickson and RoryA.

The difference is that the method you posted used to work on excel 2003 and prior editions but doesn't work on excel 2007 or later editions ... The standard solution normally used for excel 2007 and later is to use Ribbon XML. The remaining alternative is to use some ugly vba code workarounds like the one I posted.

[MENTION]Jaafar Tribak[/MENTION]
I used the method I posted on Excel 2013 and it worked. I added a msgbox before the column context menu delete(by the way that is what 294 is) and it worked.

Am I missing something?

[MENTION]mikerickson[/MENTION]
Since the .Onaction method is a application level process that might cause problems, wouldn't the _Click event that is used in the method I posted be safer?

I know the temporary item in the context menu is better, but I am kind of afraid that one day it will not deactivate correctly and show up to the users other workbooks.
So that is why I feel more comfortable with _Click event that just works within the workbook.


Thanks again and Awaiting your corrections.

 

[mikerickson]

My home machine is Mac, my work machine is Windows. The CommandBarButton does not source events on a Mac. And I haven't had time to investigate the Windows environment's handling of the CommandBarButton_Click event. But I agree that the event based approach would be safer.