How to disable right mouse on sheets at bottom click"

Kim

Revised answer 20 December 2023:

Given your problems wiith Willie's solution (which works for me), please find attached a file in which the right click is disabled.

If you open it (with macros enabled), a right mouse click will give the message "Sorry, right click not allowed!" (for demo purposes- you can later comment out the line starting MsgBox).

This code is in the Workbook section of Workbook section of the the file:

Option Explicit

Dim WithEvents App As Application


Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    Cancel = True
    MsgBox "Sorry, right click not allowed!"

End Sub

Private Sub Workbook_Open()
  Set App = Application
End Sub

This worked for me in both Excel 365 and 2010 (though I had to act on the yellow warning bar under the ribbon stating "Security Warning    Macros have been disabled  <<Enable content>>" (button).

Any file you open at the same time should also have the right mouse click disabled (since it's operation at an application level).

If it doesn't work for you (you don't get the message), check your Trust Center- you may have Macro settings item set to "Disable all macros without notification" (in which case change to "...with notication" or enable all- though that can be risky). Note that you can see macro code in VBA even with macros disabled.

If you make files from this, you can send them to others and they won't be able to use right mouse click PROVIDED macros are enabled (you can force that, see the Tutorials section).

You can also disable right mouse click for yourself for all files by copying the code into the Workbook section of the personal workbook project in VBA , called VBAProject(PERSONAL.XLSB). This however will do nothing to other users-  they need to receive a file including the code above.

Hope that fixes your problem. If so, please remember to mark this Answer as Selected. 

Hi Kim,

John has described where to put the code but I have different (simpler) code.

The code you found affects right click at the application level - all of Excel, every workbokk open while the workbook with the code is open. The code below only affects the workbook it is in. There is nothing that is turned off and then needs to be turned back on.

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

 Cancel = True

End Sub

To understand what this code means the line "Cancel = True" can be read as "cancel the right click for this workbook - Yes"

Hope this helps.

Cheers   :-)