Right Click & Copy Sheet

[poleary2000]

Can I turn off the ability to Right Click and Copy a Sheet witin Excel using VBA? This would have to work for all sheets currently in the workbook and all sheets that will be added. Please help!

 

[Damon Ostrander]

Hi Patrick,

You can disable the Move/Copy option in the right-click popup menu with the code:

Sub DisableCopy()
CommandBars("Ply").Controls("&Move or Copy...").Enabled = False
End Sub


This disables it for the Excel application, so will apply to all sheets.

Put this in a standard macro module in the workbook (keyboard Alt-TMV, then Alt-IM, paste code into VBE code pane). To run the macro go back to Excel, type Alt-TMM, select macro and Run.

Don't forget to set it back to True when done. To automatically reset it when the workbook closes, put the statement that resets it into the ThisWorkbook Close event (right-click on the Excel icon at the left end of the Worksheet Menu Toolbar, select View code and enter

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars("Ply").Controls("&Move or Copy...").Enabled = True
End Sub-

 

[poleary2000]

Great! Thank you very much for your time.

Patrick

 

[Damon Ostrander]

Hi again Patrick,

Oops. The BeforeClose should have been

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Ply").Controls("&Move or Copy...").Enabled = True
End Sub

Damon

 

[poleary2000]

Damon,

This works whenever it is pasted into the code for activating a worksheet. However, when I try to reset it to TRUE on Workbook Close or Workbook Deactivation, I receive an error stating the block was not defined.

Any insight?

 

[poleary2000]

Suggestions anyone?

 

[poleary2000]

Anyone?

I have tried other methods in the archives but can't get any to work.