Announcement

Collapse
No announcement yet.

Disable Some Right Mouse Click Options

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

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

  • #2
    Re: Disable Right Mouse Button Options

    Drop this code into the Private Module of the Workbook Object (ThisWorkbook);
    Code:
    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

    Comment


    • #3
      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.

      Code:
      Private Sub Workbook_Activate()
          With Application.CommandBars("Cell")
              .Controls("Cut").Enabled = False
              .Controls("Copy").Enabled = False
      End Sub
      Code:
      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.

      Comment


      • #4
        Re: Disable Some Right Mouse Click Options

        Good catch and point Bill, thanks!

        Comment


        • #5
          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

          Comment


          • #6
            Re: Disable Some Right Mouse Click Options

            Why can't you add them?

            Comment


            • #7
              Re: Disable Some Right Mouse Click Options

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


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

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

              Comment


              • #8
                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 "...")


                Code:
                        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
                -------------
                Averil Pretty

                Comment


                • #9
                  Re: Disable Some Right Mouse Click Options

                  Thanks Dave, works perfectly

                  Comment


                  • #10
                    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.


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

                    Comment


                    • #11


                      Re: Disable Some Right Mouse Click Options

                      CDT, Please note that the end code tag is [/code]
                      .

                      Comment

                      Working...
                      X