Macro for Right Click Paste Option...

[Pinaceous]

Is there a macro when the user copies and then when the user right clicks to paste, which can grey out the following options:
Paste (P)
Formulas (F)
Transpose (T)
Formatting (R)
Paste Link (N)


But allow only the user to select:
Values (V)
On the menu options b/c it will not be greyed out???


Please let me know, as I’m anxious to test out some scripts.
Many thanks in advance.
R/ Pin-

 

[mjbeam]

This thread may help:

Excel Disable menu functions

 

[mjbeam]

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    Application.CommandBars("Cell").Controls(5).Enabled = False

End Sub

Note: This will disable the Paste Special option in the Excel Application on all worksheets and even other workbooks. You will need to re-enable it before you can Paste Special again. Also, this only affects the Cells menu, not the column, row or other menus. You can check out this webpage for details:

restrict spreadsheet to paste values only

 

[Pinaceous]

Hi mjbeam,

I did try out these codes and the ones on your links.

However, I can't seem to get it to work after pasting it into a module.

Do you know if you might have any more codes to offer?

Thank you!

 

[mjbeam]

You don't want to paste it in a module, you need to add the middle line of code to the workshheet BeforeRightClick event.

There will be more to it that just that though. You need to have code in the appropriate events to enable and disable Paste Special as needed for your application. You will also need to disable/re-enable some of the other menu controls as I mentioned.

 

[Pinaceous]

Hi mjbeam,

I've got it and wanted to paste it!

Option Explicit
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoList As String
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
 
    On Error GoTo Whoa
 
    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
 
    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _
    Then GoTo LetsContinue
 
    '~~> Undo the paste that the user did but we are not clearing 
    '~~> the clipboard so the copied data is still in memory
    Application.Undo
 
    If UndoList = "Auto Fill" Then Selection.Copy
 
    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", _
    Link:=False, DisplayAsIcon:=False
 
    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    On Error GoTo 0
 
    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select
 
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.description
    Resume LetsContinue
End Sub

Thank you!

 

[mjbeam]

Cool, glad you got it working.

 

[Pinaceous]

VBA Excel–Allow Paste Special Only

siddharthroth rocks! awesome sight!