Disable copy/cut redux

  • Thread starter Thread starter clementefan
  • Start date Start date
C

clementefan

I'm a newbie and need some further help beyond what was given. User Ji
Rech gave user Giri some excellent code for disabling the copy and cu
functions on Excel. I must be a moron because I'm unable to figure ou
how to launch that code. User Jim Rech mentions launching the code fro
the Workbook_Open sub and re-enabling the functions from th
Workbook_Before Close sub.

Can somebody please go into more detail on that? Thanks you so much.

Clementefa
 
If you go into the VBE, and select the workbook you want to work on, you
will see an object called ThisWorkbook in the project explorer. Double click
this and it will give you a code pane where you can copy the code to. Save
the book after, and it is always there, and fires when you open/close the
workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Okay, the code is in the VBA. I open the workbook, it asks me to enabl
or disable the macro. I enable it and I'm still able to copy and past
the data in the workbook. What am i missing (other than common sense)?

Thanks for your hel
 
I think we need to see the code now.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
This is the code that was posted back in May by User Jim Rech

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

ClementeFa
 
Back
Top