Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Disable Some Right Mouse Click Options

  1. #1
    Join Date
    26th October 2004
    Posts
    93

    Disable Some Right Mouse Click Options

    Hi All, Does anyone know a way, using VBA code, to disable/enable individual shortcut menu items that are are made available with a right mouse button click. The following code disables the right mouse button completely but I would like to disable only a couple of options like the 'Cut' option.

    Thank you for any help.

    The code to disable is below and to re-enable I just change False to True.

    VB:
    Dim cb As CommandBar 
    i = 0 
    For Each cb In CommandBars 
        If cb.Type = msoBarTypePopup Then cb.Enabled = False 
    Next cb 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    Re: Disable Right Mouse Button Options

    Drop this code into the Private Module of the Workbook Object (ThisWorkbook);
    VB:
    Private Sub Workbook_SheetBeforeRightClick _ 
        (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) 
         
         
        With Application.CommandBars("Cell") 
            .Controls("Cut").Enabled = False 
            .Controls("Copy").Enabled = False 
        End With 
         
    End Sub 
    
    

  3. #3
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Disable Some Right Mouse Click Options

    The CommandBar does not reset itself. So once the comman is disabled it is not available to other workbooks even after quiting and recalling Excel. So I think the code below would work better.

    VB:
    Private Sub Workbook_Activate() 
        With Application.CommandBars("Cell") 
            .Controls("Cut").Enabled = False 
            .Controls("Copy").Enabled = False 
        End Sub 
    
    
    VB:
    Private Sub Workbook_Deactivate() 
        With Application.CommandBars("Cell") 
            .Controls("Cut").Enabled = True 
            .Controls("Copy").Enabled = True 
        End With 
    End Sub 
    
    
    Bill
    Tip: To avoid chasing code always use Option Explicit.

  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

  5. #5
    Join Date
    26th October 2004
    Posts
    93

    Re: Disable Some Right Mouse Click Options

    Thanks bill, your code works perfectly for the right click menu options that doe not have sub menus like the 'Paste Special', 'Insert' and 'Delete' controls. Are you able to say how to temporarily make tham, unavailable too please.

    Thanks for your help

    CDT

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

  7. #7
    Join Date
    26th October 2004
    Posts
    93

    Re: Disable Some Right Mouse Click Options

    I tried using the options below but I can't be coding it correctly because neither work.


    VB:
    Sub t1() 
        With Application.CommandBars("Cell") 
             
            .Controls("Insert shift cells right").Enabled = False 
             
        End With 
    End Sub 
    
    
    or

    VB:
    Sub t1() 
        With Application.CommandBars("Cell") 
             
            .Controls("shift cells right").Enabled = False 
             
        End With 
    End Sub 
    
    
    Last edited by ByTheCringe2; August 24th, 2007 at 17:15.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    26th March 2007
    Location
    Northern Beaches, Sydney
    Posts
    76

    Re: Disable Some Right Mouse Click Options

    Hi there,

    Just type in the name of the control as it appears in the right click menu (i.e. include the "...")


    VB:
    With Application.CommandBars("Cell") 
        .Controls("Cut").Visible = False 
        .Controls("Copy").Visible = False 
        .Controls("Paste").Visible = False 
        .Controls("Paste Special...").Visible = False 
        .Controls("Insert...").Visible = False 
        .Controls("Delete...").Visible = False 
        .Controls("Clear Contents").Visible = False 
         'etc
         'etc
         'etc
         
    End With 
    
    
    p.s. End code tag is [/CODE] not [CODE/]

    Cheers,
    Averil

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th October 2004
    Posts
    93

    Re: Disable Some Right Mouse Click Options

    Thanks Dave, works perfectly

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Disable Some Right Mouse Click Options

    The (…) after the word Delete indicate that the menu item when clicked will display a dialog box (not a sub menu). Therefore you cannot “disable” any selection on he dialog box. A work around would be to disable the Delete and add a custom menu selection calling a custom dialog box with what you want to allow the user to do.


    VB:
    Sub t1() 
        With Application.CommandBars("Cell") 
            .Controls("Delete...").Enabled = False 
        End With 
    End Sub 
    
    
    Bill
    Tip: To avoid chasing code always use Option Explicit.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. No Right Mouse Click
    By oldpos in forum EXCEL HELP
    Replies: 5
    Last Post: April 23rd, 2008, 16:16
  2. Action on mouse click
    By nat_m in forum EXCEL HELP
    Replies: 42
    Last Post: October 27th, 2005, 23:46
  3. Detecting a mouse click
    By QuickDraw in forum EXCEL HELP
    Replies: 1
    Last Post: August 31st, 2005, 03:59
  4. Controling mouse click
    By Pacheco in forum EXCEL HELP
    Replies: 2
    Last Post: April 3rd, 2005, 21:02
  5. Mouse right click menu
    By Epidemic in forum EXCEL HELP
    Replies: 5
    Last Post: June 13th, 2003, 03:50

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno