PDA

View Full Version : Excel 2007 Disable Cut, Copy, Paste Macro



BaronCVT
09-05-2008, 10:35 PM
I tried using the code below to prevent cut, copy, and paste in Excel 2007 (Thanks Ken). It disabled the shortcut keys just fine, but ribbon buttons (Home tab, clipboard area) were not disabled, and continued to cut, copy and paste. Has anyone else had this problem and solved it successfully?

As a side note, the reason I need to disable cut, copy, and paste (or probably just cut) is that Excel alters the formulas based on the affected cells when you cut and paste. For example: if the formula in A1 is =B1, and B1 contains 1 and C1 contains 2, then A1 = 1. But if you cut C1 and paste into B1 the forumla in A1 becomes =#REF. If there is a way to prevent Excel from changing a cell reference in a formula based on a cut and paste operation that would solve my problem and I wouldn't need to disable cut and paste.

Thanks for any help you can provide.
Chris

'*** In a standard module ***
Option Explicit

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub

'*** In the ThisWorkbook Module ***
Option Explicit

Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub

shamsam1
09-06-2008, 08:28 AM
tri this

http://www.ozgrid.com/VBA/disable-cut-copy.htm

BaronCVT
09-06-2008, 04:40 PM
It did disable 32 (or sometimes 34?) Cut menu items, but the one on the ribbon still works. I have attached a picture of the ribbon menu item I am trying to disable... perhaps I have an unusual version of Excel.

shamsam1
09-06-2008, 08:15 PM
i dont have excel 2007 installed....in home

try this ..just for cut and copy

Sub Disable_Buttons()
Dim oC1 As CommandBar
Set oC1 = Application.CommandBars("CELL")
oC1.Controls("Cu&t").Enabled = False
oC1.Controls("&Copy").Enabled = False
End Sub

BaronCVT
09-09-2008, 03:18 PM
I still can't disable cut and paste from the ribbon in Excel 2007. I tried my spreadsheet using Excel 2003 and all the cut and paste items seem to be successfully disabled - so I think it is a problem unique to Office 2007 and the ribbon.

shamsam1
09-10-2008, 05:28 AM
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

this will disable whole ribbon

then u can enable it

Carl A
09-10-2008, 07:37 AM
AFAIK:
Disabling commands on the Ribbon requires that you customize the Ribbon

Here is a site that has a short tutorial on the subject (requires membership)
http://excelusergroup.org/blogs/nickhodge/archive/tags/RibbonX/default.aspx

If you are going to do a lot of customizing to the Ribbon I recommend Ken Puls book. I have it and its a good read!
http://www.excelguru.ca/node/93