Free Microsoft Excel 2013 Quick Reference

VBA: customize context-sensitive right click popup menu

I believe it is possible to modify the context-sensitive right-click shortcut in Excel. Can anyone tell me what the simplest way to do so would be? I would like to add 2 options to the basic list (which includes Cut, Copy, Paste, Paste Special . . . etc):

Below are two simple procedures I use when working with objects. As the macro names imply, the first removes the border of the selected object (e.g., textbox), whereas the other makes the fill transparent:

Code:
Sub object_border_remove()
    Selection.ShapeRange.Line.Visible = msoFalse
End Sub

Sub object_fill_transparent()
    Selection.ShapeRange.Fill.Visible = msoFalse
End Sub
Could someone tell me how to add these 2 options to the shortcut menu? If possible, I would want to create logic that would first check to see if an object has been selected. If it has, it would add the 2 above options. Thank you in advance.

Jared


Post your answer or comment

comments powered by Disqus
I have an Excel Workbook with customized, context sensitive, right
click options. This Workbook is widely distributed to my colleagues,
and I have run into an issue on one computer so far.

While the right click options that I'm adding work perfectly fine on
every other machine so far, one computer simply does not display them.

I have checked the library references in the Excel Workbook, and none
of the required libararies appear to be missing or de-selected on this
system. I have tried re-installing the MSO.DLL file (Microsoft Office
11.0 Object Library) but this didn't solve the problem either.

All of our systems are running Windows XP with Office 2003.

If anyone has seen an issue like this or has any ideas, your comments
are appreciated.

Below is a section of the code I am using to create the custom right
click options:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
Dim cBut As CommandBarButton

On Error Resume Next
For i = 4 To 1 Step -1
If Application.CommandBars("Cell").Controls(i).BuiltI n =
False Then
Application.CommandBars("Cell").Controls(i).Delete
End If
Next i

If ActiveSheet.Index = 2 And ActiveCell.Column = 4 Then
Set cBut =
Application.CommandBars("Cell").Controls.Add(Befor e:=1,
Temporary:=True)

With cBut
If ActiveCell.Font.Bold = False Then
.Caption = "Lock Date"
Else
.Caption = "Unlock Date"
End If

.BeginGroup = True
.Style = msoButtonCaption
.OnAction = "Module6.LockSelection"

If ActiveCell.Text "" And ActiveCell.Row

Hi there

How to Creat Custom Popup Menu that appears from Cell Command(Cell Menu) ?

i.e. Appears From Mouse Right Click

Hi, Im new to Macros and tried the code from Custom Button on Right Click Menu Bar. But I closed the workbook without saving and now I have 'Useful Macros List' in the right click menu. It just brings up an error window when I hover on it.

How can I delete this from the right click list? Thanks.

Hello,

I used the commandBar to create a right-click menu on a userForm but unfortunately I get an error 400 every time I click on the option "Edit" and I don't understand what I did wrong.

Could someone please help me to resolve this small issue.

Cheers!


	VB:
	
 CreateCmdBar() 
     
    Dim st As CommandBar 
     
     'delete the pop-up if it exists
    On Error Resume Next 
    Application.CommandBars("flexgrid_rc").Delete 
     
     'Disables enabled error handler in the current procedure and resets it to Nothing. On Error GoTo 0
    On Error Goto 0 
     
    Set st = CommandBars.Add(Name:="flexgrid_rc", Position:=msoBarPopup, Temporary:=False) 
     
     'add two menu items to the new commandbar
    With st 
         
        .Controls.Add Type:=msoControlButton 
         
         'assign captions to the menu items and OnActions
        .Controls(1).Caption = "Edit" 
         
         'please note that the following OnAction macros would have to be created
        .Controls(1).OnAction = "test2" 
         
    End With 
     
    st.Enabled = True 
     
End Sub 
 
Sub test2() 
    MsgBox "test" 
End Sub 
 
Private Sub UserForm_Initialize() 
    CreateCmdBar 
End Sub 
 
Private Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 
    If Button = 2 Then 
        CommandBars("flexgrid_rc").ShowPopup 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello all,

I want to disable customize option in toolbars, means right click on
tool bar and select customize option. now i want to disable that
customize option.

Check the screen shot attachment.

Thanks&Regards,
Amar...

+-------------------------------------------------------------------+
|Filename: Customize disable.doc |
|Download: http://www.excelforum.com/attachment.php?postid=3923 |
+-------------------------------------------------------------------+

--
areddy
------------------------------------------------------------------------
areddy's Profile: http://www.excelforum.com/member.php...o&userid=28204
View this thread: http://www.excelforum.com/showthread...hreadid=477806

Everyone,

Below is some code that I've written to add a new right-click shortcut menu item that opens a file based on the data in the active cell. My goal is to have this right click functionality available on any sheet in any workbook without needing the user to add the code to each workbook. Here's the code from personal.xls that adds the right click functionality (emphasis added):

Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
        Cancel As Boolean)
 
    For Each icbc In Application.CommandBars("cell").Controls
        If icbc.Tag = "brccm" Then icbc.Delete
    Next icbc
 
        With Application.CommandBars("cell").Controls _
            .Add(Type:=msoControlButton, before:=6, _
                temporary:=True)
           .Caption = "Open in AutoVue"
           .OnAction = "GetDrawing"
           .Tag = "brccm"
        End With
 
End Sub
The problem is that the rightclick code above isn't really what's being run and I'm really confused by that. I had changed the "temporary:=True" above to false, and saved everything as an addin (.xla file) to see if it would easily port to other machines. My memory of the exact sequence of events is hazy, so I can't give an accurate timeline of what I did when. I think that when I mucked around with the temporary true/false setting, it got applied to some other persistent location that I can't find. My symptoms are as follows: any changes to the rightclick code above do not affect the project (I can change caption:= to "Change change change" and the rightclick menu item still has the caption "Open in AutoVue"), removing the personal.xls file from the XLSTART folder does not remove the rightclick menu item, and removing the .xla file from the ...MicrosoftAddins folder doesn't get rid of it either.

I'm happy that the code works with any new workbook I open in excel, but I'm unhappy that I've created some sort of immovable object that can't be changed or removed. I need to understand how the menu item got there and what I can do to remove/modify it so that I can figure out the correct method to give the code to other users on other machines. If anyone can offer some suggestions, advice, or other places to look for help, I'd be eternally grateful!

Thanks,
Adam Hartman

Hello all, is it possible to add "insert time" and "insert date" to the
right click popup menu? I am trying to get away from keyboard
shortcuts.
TIA
Kezza

Hello all,

I want to disable customize option in toolbars, means right click on tool bar and select customize option. now i want to disable that customize option.

Check the screen shot attachment.

Thanks&Regards,
Amar...

Hi

I wish to make it so that you can access a list of macros from the Right-Click (Also known as Context-Sensitive) Menu bar.

I have kinda gotten it to work. The macro button and drop down list is added and you can view it if you select a cell or range of cells on the sheet, but if use use the tabs to select entire columns/rows or to select all cells on a sheet then the extra button is no longer there.

It's got me confused. It wouldn't be so big a problem if it wasn't for the fact that some of the macros in my list require entire columns/rows or all cells to be selected when they're activated.

Here's part of the code I use to add the button and macro list to the menu. Both appear when selecting cells and ranges. But they're just not there if you select entire column/rows or all cells


	VB:
	
 CreateMenuTest() 
     
    Dim cbcRightClick As CommandBarControl 
     
    Set cbcRightClick = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup) 
     ' Give the control a caption and sets the "isRightclick" variable to "True"
    cbcRightClick.Caption = "Useful Macros List" 
     
    Const ArrayNum = 9 'Add number here to increase array size and add a new macro
    Dim NameArray(ArrayNum) As String 'Array to hold button names
    Dim MacroArray(ArrayNum) As String 'Array to hold macro names
    Dim ButtonCount As Integer 'Counter for adding buttons
     
     'This loops through the 2 arrays adding the names and macro links to each entry
    For ButtonCount = 1 To ArrayNum 
        NameArray(ButtonCount) = Choose(ButtonCount, _ 
        "Add/Remove Negative Values", _ 
        "Lock/Protect Formulae", _ 
        "Locate/Remove Blanks", _ 
        "Highlight Duplicate Entries", _ 
        "Prevent Duplicate Entries", _ 
        "Fix Dates", _ 
        "Find/Break External Links", _ 
        "Accounting Bracketed Negatives", _ 
        "Trim Cells (Removes blanks within cells)" _ 
) 
         
        MacroArray(ButtonCount) = Choose(ButtonCount, _ 
        "Manipulating_Negatives", _ 
        "Lock_Formulae", _ 
        "Remove_Blanks", _ 
        "Highlight_Duplicates", _ 
        "Prevent_Duplicates", _ 
        "Convert_To_Date", _ 
        "Break_Links", _ 
        "AccountingNegativesInBrackets", _ 
        "Trim_Blanks" _ 
) 
    Next ButtonCount 
     'End of loop adding data to arrays
     
     'Loop to add the buttons to the right click cell drop down menu
    For ButtonCount = 1 To ArrayNum 
        With cbcRightClick.Controls.Add 
            .Caption = NameArray(ButtonCount) 
            .OnAction = MacroArray(ButtonCount) 
        End With 
    Next ButtonCount 
     
End Sub 
 
Sub DeleteTestMenu() 
    On Error Resume Next 
    Application.CommandBars("Cell").Controls("Useful Macros List").Delete 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Ignore the fact that none of the macro links work, if you just paste this into a new module you should be able to see what I mean

I'm using Excel 2003 and am unable to find the name of, and therefore customise, the right-click shortcut menu that pops up when you right-click on a line drawn from the 'Drawing" toolbar. Can anyone help please. Basically, I'm trying to do some technical analysis on a chart. I want to be able to right-click on the lines that I draw on the chart and replicate a parallel line. thanks..

Hi all,

I'd like to run my Macro (say Main) by right-clicking any cell in any sheet in my workbook. I don't want a Right-click event in "This Workbook" to automatically run Main, but rather that the name "Main" should appear as one of the choices in the context menu that pops up on right-clicking a cell.

I also want this feature to be disabled before closing the workbook in question so that the option is not available when the workbook isn't open. I know that this can be done. I just don't know how.

Help awaited.

Regards,

m

When I run an excel vba form, the right click menus dont show on text boxes. Is there a workaround using windows API to enable copy paste menu on right click event?

I appreciate your help

Hello,

Is there any VBA code to hide right click menu items (right click on worksheet) when the workbook is activated and restore the originals when workbook is de-activated ?

similarly is there any VBA code to add a custom command to the right click menu ?

thanks in advance,
bansh

I have an odd problem.

I no longer have the option to assign macros in the right-click context menu.
When I create a custom button on an excel sheet and then enter design mode, usually you can just record a macro, right click a button and assign a macro. I cant seem to find the option to assign macros to buttons!

Any help on enabling this option in the right-click contextual menu would be much appreciated.

Regards.

Hi,

I have customized the right click menu.For e.g

Menu1->Child1->grandchild1
->grandchild2
->grandchild3

Now, I want to move grandchild3 to 1st position, i.e.

Menu1->Child1->grandchild3
->grandchild1
->grandchild2

Can you please how to moe the position of CommanBarcontrols dynamically.

Hello,

I'd like to add contextual entries in the right-click excel menu, ie:

When selecting 2 cells and right clicking, I'd like the two first
entries in the menu to be VBA functions that I designed

Could you help ?

Thanks,

vobiscum

--
Vobiscum
------------------------------------------------------------------------
Vobiscum's Profile: http://www.msusenet.com/member.php?userid=2459
View this thread: http://www.msusenet.com/t-1870552916

Would it somehow be possible to trigger an event on right-clicking a
commandbar button?

These buttons are created like this:

Public cb2 As CommandBar

Set cb2 = CommandBars.Add("MyOptionsPopUp", _
msoBarPopup, _
MenuBar:=False, _
temporary:=True)

With cb2
Set FileControl = .Controls.Add(Type:=msoControlPopup)
With FileControl
.Caption = "File"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Open report (F3 OR O from the treeview)"
.OnAction = "OpenReport"
.FaceId = 23
End With

etc.

The purpose is to trigger context sensitive help on the menu.

RBS

Users should be able to customize choices available when right clicking in
the spreadsheet area of Excel. More frequently used menu choices would be
very helpful. I have never used "Clear Contents" or "Pick from List". It
would be more efficient to a user to be able to add their own choices such as
"Pivot Table" or "Text to Columns". However, as it is right now, Excel is
still an unbelievable product. Good Luck.

Hi Everyone!

I have a sheet that I need to protect deletion for rows and columns. I would like to have a macro to disable the delete for rows and column feature from the right click pop up menu for the particular workbook only. Once this particular workbook is closed, it should enable the delete for rows and column feature back.

I appreciate everyone's help.

Thank you in advance.

RNF

For some reason my right click shortcut menu had been disabled (not through my own doing). How do I enable this again?

Hi,

How can I make the standard right-click with the mouse MENU absolutely
disabled.
I do not want the user may use the functions in it.

Thanks in advance

Robert

Thanks to Juan P and others I can add menu items to the right click of my mouse

this works for me

Sub addtomouse()
With Application.CommandBars("Cell").Controls.Add
.Caption = "testmacro"
.OnAction = "macro1"
End With
End Sub

WHat I want is these two options available on right click

1. display list of macros (tools/macro/macro)
2. goto cell A1, view to top of page

Ok,with the above code I know can achieve this by writing macros and listing them in the drop down menu.

However this means every worksheet must have these modules in them or I need the file open that has them.

But I wondered if these two options could be written in the code so that it would work on any worksheet I opened.

(is this "hard coded"?)

As I said this is probably just my ability to get obsessed by irrelevant issues but if some in could indulge me I would be eternally grateful!

thanks kd

Hi all,

When you right-click you get Cut, copy, etc items which can be accessed with
CommandBars("Cell") command. Fine!

What is the name for right-click contextual menu when a whole row/column is
selected? When I apply CommandBars("Cell") over hidden rows/columns it
doesn't contain items like Hide/Unhide items!

Thanks for your time.

Can anyone help me please? The Insert menu when I use my right click is
shaded meaning I can't use it anymore when I want to insert a row. I
normally highlight a row and use this Insert function with the right click.
I do not have this Essbase thing installed so it's not that. I tried some
suggestions on adding macro but it didn't work either. It used to be there
for me but not anymore. The interesting thing is if I want to insert a
column and highlight a column, the Insert function is available to me.

Can someone please help me figure this out? I've been trying to fix this
since last week.

Thanks!


No luck finding an answer? You could always try Google.