Code Error - Run Time Error 5 (Disable Cut, Copy & Paste)

[Tim]

I'm stuck!

I have a code (below) to disable the cut copy and paste features in a
workbook.
1. from the edit menu
2. the actual comand buttons (the toolbar buttons)
3. ctr c etc
4. right click options.

My prombem is that I get a runtime error 5 saying that this is an
invalid proceedure.
Any help on the bug here would be appreciated.
Thanks
Tim


Private Sub Workbook_Activate()
Application.CommandBars("Standard").Controls("Cut").Delete
Application.CommandBars("Standard").Controls("Copy").Delete
Application.CommandBars("Standard").Controls("Paste").Delete

Application.CommandBars("Formatting").Controls("Cut").Delete
Application.CommandBars("Formatting").Controls("Copy").Delete
Application.CommandBars("Formatting").Controls("Paste").Delete

Application.CellDragAndDrop = False
Application.CutCopyMode = False
Application.OnKey "^X", ""
Application.OnKey "^C", ""
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars(1).Reset
Application.CellDragAndDrop = True
Application.CutCopyMode = True
Application.OnKey "^X", "^X"
Application.OnKey "^C", "^C"
End Sub

 

[Greg Wilson]

I may be missing somenting here but there
aren't "Cut", "Copy", "Paste" controls in the Formatting
menu - at least not in my version of Excel.

Also, in point 1 you say you disable the cut, copy and
paste features from the Edit menu. However, your code
doesn't do this. In point 4 you say you disable "right
click options" but your code doesn't do this. Did you
mean CommandBars("Cell") which is the right-click popup
menu? Or did you think Application.CutCopyMode = False
would disable these features?

You also don't reset the Standard or Formatting tool bars
in the Deactivate event. You only reset the Worksheet
Menu Bar.

Suggested is as follows. Note that I use the
DragAndDropStatus variable to hold the state of the
CellDragAndDrop option (True of False) and only return it
to its original state in the Deactivate event. I don't
believe your syntax was correct re the OnKey method. Note
the curly brackets and lower case (user isn't likey to use
upper case and lower case will work). It probably isn't
necessary to reset the OnKey items because I believe they
reset automatically on close (or open). I reset them
anyway at least for demo purposes.

Dim DragAndDropStatus As Boolean
Private Sub Workbook_Open()
With Application
DragAndDropStatus = .CellDragAndDrop
..OnKey "^{x}", ""
..OnKey "^{c}", ""
..CellDragAndDrop = False
With .CommandBars("Cell")
..Controls("Cut").Enabled = False
..Controls("Copy").Enabled = False
..Controls("Paste").Enabled = False
End With
With .CommandBars("Worksheet Menu Bar").Controls("Edit")
..Controls("Cut").Enabled = False
..Controls("Copy").Enabled = False
..Controls("Paste").Enabled = False
End With
End With
End Sub

Private Sub Workbook_Deactivate()
With Application
..CellDragAndDrop = DragAndDropStatus
..CommandBars("Cell").Reset
..CommandBars("Worksheet Menu Bar").Reset
..CommandBars("Standard").Reset
..OnKey "^{x}" 'Probably not actually necessary
..OnKey "^{c}" 'Same
End With
End Sub

Not rigorously tested and never used by me for any project.

Regards,
Greg
VBA Amateur

 

[Tim]

Thanks Greg...
Works just fine.
I also tried the line
..Controls("Office Clipboard").Enabled = False
to not allow the user to paste from the clipboard, as they can with
this code but I get an error.
Any other ideas?

Thanks & Regards
Tim Bray

 

[Ivan F Moala]

Tim
Try the routine here

http://www.xcelfiles.com/VBA_Quick13.html

 

[Tim]

Thanks Ivan,

A great help.
Regards
Tim

Tim
Try the routine here

http://www.xcelfiles.com/VBA_Quick13.html