DoktorPhill
New Member
- Joined
- Sep 23, 2020
- Messages
- 6
- Office Version
- 365
- 2010
- 2007
- Platform
- 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:
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:
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)
The problem is that whenever there is something on the windows clipboard (not the office clipboard), it shows the Paste Options button:
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