vba code to make invisible 'paste option' from the right click menu?

Hi,

try this..

Private Sub Workbook_Open()
Application.CommandBars("Cell").Controls("Paste Options:").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Paste Options:").Enabled = True
End Sub

Hi TasosK,

Thanks for the quick reply.

But  the above code is not worked am getting the below error when debegging.

Invalid procedure call or argument.

No,

place this code in BeforeClose event

and not in BeforeRightClick event

XXXXXXXXXXXX

double click on ThisWorkbook (in vba)

and paste in the below TWO Codes

Private Sub Workbook_Open()
Application.CommandBars("Cell").Controls("Paste Options:").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Paste Options:").Enabled = True
End Sub

Save - Close and re-open your WB

NOTE: THIS IS A TEMPORARY SOLUTION, ONLY FOR THIS WORKBOOK.

Hi TasosK,

I have tried the above code even then I am getting same error when opening and closing.

Hi,

Sorry, i haven't check the above codes.
because, i'm using xl2003

did you check if that works
for Cut,  Copy ?

e.g

Private Sub Workbook_Open()
Application.CommandBars("Cell").Controls("Cut").Enabled = False

Application.CommandBars("Cell").Controls("Copy").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Cut").Enabled = True

Application.CommandBars("Cell").Controls("Copy").Enabled = True

End Sub

Hi TasosK

I have tried cut, copy code, its working but 'paste option:' code is not working.

Application.CommandBars("Cell").Controls("Paste").Enabled = False this is working in Excel 2007 but not in 2010.

In office 2010 they have newly introduced 'paste option:' instead of 'paste' .  so please look the problem and provide the solution.

Id for paste is 22 like that if you know id for the "paste option:" please share

Thanks.

Hi TasosK

Kindly check this issue in office 2010/2013 instead of 2003/2007.

Thanks.

Hi,

i'm using xl2003
so,
i can't see the 'right click' options, of xl2010
but the problem is, that i can't test this code.

Hi,

i suppose that, someone in this forum
could solve the issue.

(maybe, without  using an add-in)

TasosK