customizing the right-mouse drop-down menu

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top