Issue when modifying the right click menu

DoktorPhill

New Member
Joined
Sep 23, 2020
Messages
4
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
So I've got my fancy custom right click menu working by hiding the existing options and adding in my custom buttons, working great, but I don't want my users to be able to paste anything into the workbook.

The problem is that whenever there is something on the windows clipboard (not the office clipboard), it shows the Paste Options button:
1685252815789.png

Research shows that you can't disable this, so I thought that I could circumvent it by clearing the windows clipboard before right click using the following:
VBA Code:
#IF VBA7 then
Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
#Else
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
#End If

Public Sub ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub

However calling ClearClipboard in the Workook_SheetBeforeRightClick sub prevents the right click menu from showing up at all, even though the code runs all the way to the end. Commenting out the ClearClipboard allows the right click menu to be shown again just fine. I've tried making it a function, adding a sleep timer and/or a wait timer, no dice. I've also tried clearing the clipboard on selection change, but this creates another issue where users can't open the right click menu without selecting the desired cell first.
Anyone tried this in the past?

(My before right click sub)
Code:
Private Sub Workbook_SheetBeforeRightClick(Byval Sh as Object, Byval Target as Range, Cancel as Boolean)
    ClearMenu 'Clears custom buttons
    Application.ShowMenuFloaties = True

    If ActiveSheet.Name = "OnScreen" Then
        ClearClipboard
        RightClickMenu 'hides default Right click menu and generates custom right click buttons
    Else
        Application.CommandBars("Cell").Reset
    End If
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Forum statistics

Threads
1,214,833
Messages
6,121,868
Members
449,054
Latest member
juliecooper255

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top