You would put all my code in a standard module and then call sub
SetEunuchMode when you wanted the user unable to copy/cut. If you want that
to occur when they open the workbook then call it from either sub Auto_Open
(if you want one) or the Workbook_Open sub in the ThisWorkbook module. Now,
as you know, the user can disable macros, so there is no way to guarantee
that this code will run, but there is nothing we can do about that.
You definitely have to run sub SetRegMode before the user closes Excel or
the menu items will remain disabled when the user restarts Excel! So call
this sub from the Workbook_BeforeClose sub in ThisWorkbook or from sub
Auto_Close if you have one.
--
Jim Rech
Excel MVP
| Hi Jim Rech,
|
| Could you kindly tell me how to put the code in the macro.
| Should i put it in the open workbook event & then have
| individual calls for the subroutines? Further, my
| understanding is that this does not change the system
| settings apart from the workbook i.e i mean would it
| restore the original excel settings ?
|
| Thanks a million for the help. I have been looking for
| something like this since the last 2 days.
|
| Regards,
| Giri
|
| >-----Original Message-----
| >Disabling Cut is even more important than Copy, in my
| opinion, but both are
| >necessary to protect your app. When you go this route
| you have to disable
| >the menu items for copy/cut and the shortcut keys. Also
| drag and drop (and
| >therefore the Options dialog where the user could turn
| D&D back on). You
| >should also disable access to customizing the toolbar
| since the user could
| >use the Reset command to undone your menu disables.
| >
| >I think this does everything. You can remove the Excel
| 2002 stuff if you
| >want. Someone let me know if I let anything through the
| cracks<g>.
| >
| >Dim DragDrop As Boolean
| >
| >Sub SetEunuchMode()
| > CopyCutOff False
| >End Sub
| >
| >Sub SetRegMode()
| > CopyCutOff True
| >End Sub
| >
| >Sub CopyCutOff(OnOff As Boolean)
| > SetCtrl 21, OnOff ''Cut
| > SetCtrl 19, OnOff ''C
| > SetCtrl 522, OnOff ''Options
| > CtrlKeys OnOff
| > CtrlDragDrop OnOff
| >
| > ''This controls whether
| > ''View, Toolbars is enabled
| > ''Commandbars context menu is enabled.
| > CommandBars("Toolbar List").Enabled = OnOff
| >
| > If Val(Application.Version) >= 10 Then
| SetDisableCustomize OnOff
| >End Sub
| >
| >Sub SetCtrl(CtrlNum As Integer, Allow As Boolean)
| > Dim Ctrls As CommandBarControls
| > Dim Ctrl As CommandBarControl
| > Set Ctrls = CommandBars.FindControls(, CtrlNum)
| > If Not Ctrls Is Nothing Then
| > For Each Ctrl In Ctrls
| > Ctrl.Enabled = Allow
| > Next
| > End If
| >End Sub
| >
| >Sub CtrlKeys(Allow As Boolean)
| > With Application
| > If Allow Then
| > .OnKey "^x"
| > .OnKey "+{DELETE}"
| > .OnKey "^c"
| > .OnKey "^{INSERT}"
| > Else
| > .OnKey "^x", ""
| > .OnKey "+{DELETE}", ""
| > .OnKey "^c", ""
| > .OnKey "^{INSERT}", ""
| > End If
| > End With
| >End Sub
| >
| >Sub CtrlDragDrop(Allow As Boolean)
| > With Application
| > If Allow Then
| > .CellDragAndDrop = DragDrop
| > Else ''Kill
| > DragDrop = .CellDragAndDrop ''Save user's
| setting
| > .CellDragAndDrop = False
| > End If
| > End With
| >End Sub
| >
| >''Must be in a sub that is only called if running Excel
| 2002 or later
| >''This controls whether
| >'' Customize menuitem appears on View, Toolbars **
| >'' Customize menuitem appears on commandbars context
| menu **
| >'' Customize dialog appears when toolbar area is double-
| clicked
| >'' Tools, Customize is enabled
| >''** These two effects aren't actually needed when
| >'' CommandBars("Toolbar List").Enabled = False is run
| as
| >'' above since the entire Toolbars menu is disabled
| but
| >'' no harm and we need the other two effects.
| >Sub SetDisableCustomize(OnOff As Boolean)
| > Application.CommandBars.DisableCustomize = Not OnOff
| >End Sub
| >
| >--
| >Jim Rech
| >Excel MVP
| >
| message
| >| >| Hi,
| >|
| >| Any way that we can disable copy from the code? We are
| >| using Office 2000 and Windows 2000 Professional.
| >|
| >| Note: I had posted this question earlier, but didnt seem
| >| to get a solution. Thought i should post it once more,
| >| just in case...
| >| I apologize to users who are reading it again.
| >|
| >| Appreciate any solution.
| >|
| >| Regards,
| >| Giri
| >
| >
| >.
| >
