Disable Cut & Copy then restore on exiting
Hello all, If you need to disable both the Cut & Copy features of Excel then the following VBA works fine - I have used this to stop people cutting cells and pasting to other worksheets then saving the file - causing errors. -- Copy the following code into the "ThisWorkbook" page -- Private Sub Workbook_Open() Run "Workbook_Activate" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Run "Workbook_Deactivate" End Sub -- Copy the following code into a new "Module" -- Private Sub Workbook_Activate() Dim oCtrl As Office.CommandBarControl 'Disable all Cut menus For Each oCtrl In Application.CommandBars.FindControls(ID:=21) oCtrl.Enabled = False Next oCtrl 'Disable all Copy menus For Each oCtrl In Application.CommandBars.FindControls(ID:=19) oCtrl.Enabled = False Next oCtrl Application.CellDragAndDrop = False End Sub Private Sub Workbook_Deactivate() Dim oCtrl As Office.CommandBarControl 'Enable all Cut menus For Each oCtrl In Application.CommandBars.FindControls(ID:=21) oCtrl.Enabled = True Next oCtrl 'Enable all Copy menus For Each oCtrl In Application.CommandBars.FindControls(ID:=19) oCtrl.Enabled = True Next oCtrl Application.CellDragAndDrop = True End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) With Application .CellDragAndDrop = False .CutCopyMode = False 'Clear clipboard End With End Sub Save the file then exit Excel - restart the file - the copy & cut functions should now not work. -- sparx ------------------------------------------------------------------------ sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787 View this thread: http://www.excelforum.com/showthread...hreadid=537616 |
All times are GMT +1. The time now is 08:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com