Attempting to customize context menu

[Nate123456]

Hello!

I am currently trying to customize the "right click" or context menu for my project in excel 2010. After doing some online research, I was able to come across some code that seemed fairly straightforward to me for getting rid of menu options I didn't want and adding new ones in.

My overall goal is to get it so that the only options for right clicking a cell are to display, say, 3 options (for now) that run code in VBA. I got almost everything removed, and I was able to figure out how to add new options as well. I'm currently stuck on the "paste options:" section. Looking online, I was not able to find any way to get rid of that particular option.

I was able to use the following example code to get rid of everything else:

      Application.CommandBars("Cell").Controls("Pick from Drop-Down List...").Delete

and the following code to add new stuff:

Sub AddItemToContextMenu()
    Dim cmdNew As CommandBarButton
    Set cmdNew = CommandBars("cell").Controls.Add

    With cmdNew
        .Caption = "Test1"
        .OnAction = "UserForm1load"
        .BeginGroup = True
    End With

however I am still left with "paste options:" and the buttons associated with it (i.e. the little keep text only button for example). Looking online, I found a list of IDs that reference different parts of the context menu (https://support.microsoft.com/en-us/kb/213552), but none of the ones that sounded remotely close did anything to the paste options section. most relevant resources out there describe how to add options, not how to wipe and only setup a few of your choosing.

I'm either wondering if someone out there has a way to get rid of the "paste options:" section, or perhaps a workaround code that wipes out/replaces all the options with new ones. I've searched and searched to no avail

also, that extra popup about the context menu that has the font, color, font size, etc- I'd like to get rid of that too if possible.

Here's what it looks like currently (arrows indicate what I'm looking to get rid of).

Thank you for your time!

Nathan

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Have you seen this from Ron de Bruin? Creating a PopUp Menu that is working in every Excel version

It creates a pop-up menu which you can call from a right-click event:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Call CreateDisplayPopUpMenu
    Cancel = True
End Sub

The above goes into the ThisWorkbook event module.

I think you only need this bit of Ron's code:

Public Const Mname As String = "MyPopUpMenu"

Sub DeletePopUpMenu()
    'Delete PopUp menu if it exist
    On Error Resume Next
    Application.CommandBars(Mname).Delete
    On Error GoTo 0
End Sub

Sub CreateDisplayPopUpMenu()
    'Delete PopUp menu if it exist
    Call DeletePopUpMenu

    'Create the PopUpmenu
    Call Custom_PopUpMenu_1

    'Show the PopUp menu
    On Error Resume Next
    Application.CommandBars(Mname).ShowPopup
    On Error GoTo 0
End Sub


Sub Custom_PopUpMenu_1()
    Dim MenuItem As CommandBarPopup
    'Add PopUp menu
    With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
                                     MenuBar:=False, Temporary:=True)

        'First add two buttons
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 1"
            .FaceId = 71
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 2"
            .FaceId = 72
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

        'Second Add menu with two buttons
        Set MenuItem = .Controls.Add(Type:=msoControlPopup)
        With MenuItem
            .Caption = "My Special Menu"

            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Button 1 in menu"
                .FaceId = 71
                .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
            End With

            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Button 2 in menu"
                .FaceId = 72
                .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
            End With
        End With

        'Third add one button
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 3"
            .FaceId = 73
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

    End With
End Sub


Sub TestMacro()
    MsgBox "Hi There, greetings from the Netherlands"
End Sub

 

[Nate123456]

Thank you for your reply!

I looked through the resource you linked and the information you posted directly, and I came to the conclusion that the method used would be to setup an alternate shortcut menu that's called instead of the normal cell right click menu. At this point, in the way I've got my project setup, it's better for me to adapt the normal excel default right click menu rather than create a completely custom one. That's not to say that your post's advice doesn't technically solve my issue, it just goes about it in a way I'd rather not prefer if I can help it

I was looking through the linked article, and I noticed this section:

Sub DeleteFromCellMenu()
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl

    [COLOR=black]'Set ContextMenu to the Cell menu[/COLOR]
    Set ContextMenu = Application.CommandBars("Cell")

    [COLOR=black]'Delete custom controls with the Tag : My_Cell_Control_Tag[/COLOR]
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "My_Cell_Control_Tag" Then
            ctrl.Delete
        End If
    Next ctrl
End Sub

I realized that I could adapt this to deleting every option for the right click menu. Here's the adapted version:

Dim ContextMenu As CommandBar
Set ContextMenu = Application.CommandBars("Cell")
 Dim Ctrl As CommandBarControl
    For Each Ctrl In ContextMenu.Controls
    Ctrl.Delete
    Next Ctrl

Pretty simple stuff, right? I pasted that in, ran it, and my right click menu was completely empty!

I've got a bunch of if/then statements setup that provide options based on what the cell contains, so I didn't want to adapt that to all that code that the article had, plus it would have taken me a while to figure out exactly what all that code was doing so I could convert it to my own project, and this seemed much more simple to me.

I really appreciate your help, you got me to look at the article you linked it greater detail and allowed me to find the fix I preferred.

Thank you!!!

I'm still looking for how to delete that second thing I pointed out in that picture I attached in the first post- the extra popup window with the font, color, size, etc.

Any ideas on that?

Progress Update-

I found a way to get rid of that thing easily once I found out what it was called- the "Mini Toolbar"

Application.ShowMenuFloaties = True

True makes it hide it, false makes it show it.

Thank you so much for all your help!!!!

 

[RickXL]

Great news!!

Thanks for the update.

I would like to be an Excel expert one day but just when I think I must be getting close I find another whole area that I know nothing about!

I have just started looking at customizing the Ribbon. Now I discover that you can change the context menus or make entirely new ones as well.

I will keep a note of your post for when I get that far

Thank you.

Regards,