Tim
Try the routine here
http://www.xcelfiles.com/VBA_Quick13.html
(E-Mail Removed) (Tim) wrote in message news:<(E-Mail Removed)>...
> 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" <(E-Mail Removed)> wrote in message news:<214301c4277b$426236d0$(E-Mail Removed)>...
> > 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
> > >.
> > >
Similar Threads
