customizing the right-mouse drop-down menu

[lalbatros]

Hello,

I would like to add some customized items to the right-mouse drop-down menu.
How could I do that in VBA?
I have already some experience for building normal menus, but not mouse menus.

Thanks

 

[GTO]

Greetings,

Just a simple example of adding a control to the "Cell" menu, which is the shortcut menu for the sheet. If you were looking to add a control to the shortcut menu for the sheet's tab, then the command bar is "Ply".

For the "Cell" commandbar, this would go in the sheet module, so as to only have the control available (existing) for the individual sheet.

Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim btn As CommandBarButton

    Set CB = Application.CommandBars("Cell")
    
    Set btn = CB.Controls.Add(Type:=msoControlButton, Temporary:=True)
    With btn
        .BeginGroup = True
        .Caption = "My Control"
        .FaceId = 333
        .OnAction = ""
        .Style = msoButtonIconAndCaption
        .Tag = "Tag1"
    End With
        
    
End Sub

Private Sub Worksheet_Deactivate()
Dim CB As CommandBar
Dim ctl As CommandBarControl

    Set CB = Application.CommandBars("Cell")

    For Each ctl In CB.Controls
        If ctl.Tag = "Tag1" Then ctl.Delete
    Next
    
End Sub

Hope this helps,

Mark

 

[lalbatros]

Thanks a lot Mark, this will help very well.

Would you have some idea about how to make the content of the commandbar depend on the activecell?
Should I rebuild the menu everytime the selection changes?
Or would it be possible to arrange the menu only when it is used?

Thanks

 

[lalbatros]

I solved that problem.
In the previous version of my application, I had a custom menu located in the "Worksheet Menu Bar".
Simply moving it to the "Cell" commandbar is enough.
I can now concentrate on improving the submenus.

Thanks Mark, again.

 

[GTO]

You bet

Sorry I couldn't answer back, got busy. In short to #3:

Everyone has a different way of doing things of course - what I look at is:

If I want the same custom commands to be available for severa sheets, or specific ranges in them, then look at putting the code under ThisWorkbook Activate/Deactivate sgeet and select case for what commands to visible=true.

If this is the case, then use the visibility instead of deleting the command, until the workbook is deactivated.

Otherwise if its just a specific sheet/range, then just wreck the command at sheet deactivate.

Sounds like you are already well on your way

Mark