About Us

Tim[_36_]

Thanks Ivan,

A great help.
Regards
Tim

(Ivan F Moala) wrote in message m...
Tim
Try the routine here

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

(Tim) wrote in message . com...
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

"Greg Wilson" wrote in message ...
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

-----Original Message-----
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
.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy paste error	ganeson r	Charts and Charting in Excel	1	December 13th 09 08:25 AM
Filtered copy and paste error	Bagia	Excel Discussion (Misc queries)	1	October 21st 09 04:34 PM
copy and paste error	jenny	Excel Discussion (Misc queries)	2	January 16th 09 01:53 PM
Copy/Paste error	Anthony	Excel Worksheet Functions	2	February 7th 07 06:46 PM
Error in Copy/Paste	Thierry zen Ruffinen	Excel Programming	0	April 5th 04 09:46 AM