Prevent Cut/Copy/Move Macro Code

Timbo · Sep 8th 2006

Every year I reach this time of year when I have to issue template workbooks to people for budgeting purposes.

Within the workbooks are various numbers of worksheets pre -formatted and ready for these people to enter data.

Some of the data in the worksheets is important to them as individuals but not to me so I have a series of macros that lift the information from the worksheets and put it into a worksheet more specific to me.

Because the users are on the whole not that good with Excel I have put in easy to use look up tables and various proctections to stop them adding or deleting rows or columns as this plays havoc with my macros.

There is one thing I have "so far" been unable to do and I wondered if anyone could help.

Is there a macro that I could put somewhere in the workbook which would detect when someone tried to cut and paste and would either put up a message box or stop them doing this?

The reason being they are cutting from one row to another and this is messing up the calculations which are protected.

Is there also a way to stop them changing the name on the sheet tab in the smae way?

Many thanks.

**Krishnakumar** · Sep 8th 2006

Re: Prevent Certain Actions Macro

Hi Tim,

See http://www.ozgrid.com/VBA/disable-cut-copy.htm

HTH

johnccc · Sep 8th 2006

Re: Prevent Certain Actions Macro

Hi,

Regarding the last point, you could use the BeforeSave event to run through each sheet by it's number and rename it with the text you require. It would not stop them renaming but it would stop them saving the changes.

John

VBA New Kid · Sep 8th 2006

Re: Prevent Certain Actions Macro

The following should work to stop the cut and paste. Put it in ThisWorkbook:

Code

Private Sub Workbook_Open()


    'Disables cut menu options
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
        Ctrl.Enabled = False
    Next Ctrl


    'Disable drag and drop
    Application.CellDragAndDrop = False
    
    'Disable <CTRL> X
    With Application
        .OnKey "^x", "Message"
    End With


End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)


    'Re-enables all cut menus when this spreadsheet is closed


    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21)
        Ctrl.Enabled = True
    Next Ctrl
    
    Application.CellDragAndDrop = True
    
End Sub

Display More

In a module, type the following to display an error message:

Code

Sub Message()
Dim Msg As String
'Display message whenever <CTRL> X is used on specified worksheets
    Msg = MsgBox("Sorry, this command has been disabled to" & vbNewLine _
    & "protect the integrity of this spreadsheet.", vbOKOnly, "Disabled Keyboard Command")
End Sub

Re them changing the worksheet name, are you able to password protect the workbook? This will stop them adding, deleting or renaming worksheets.

Timbo · Sep 11th 2006

Re: Prevent Certain Actions Macro

Thanks for the advice, very useful.

Dave Hawley · Sep 11th 2006

Re: Prevent Certain Actions Macro

Just be sure Ctrl X is not configured for another action as is sometimes the case on other PCs.

That is why I haven't used SendKeys and used Selection Change on the page below

Disable Cut/Copy in Excel Workbooks

Timbo · Sep 11th 2006

Re: Prevent Certain Actions Macro

Hi Dave,

Thanks for the tip I haven't started work on this yet but Ctrl X is indeed configured to select and copy.

Thanks again.

Timbo · Sep 15th 2006

Re: Prevent Certain Actions Macro

Hi Dave,

I have pasted the macro into Thisworkbook in the VBA Editor and I have saved and closed the file.

When I re0open it I get this error -

Runtime error - '214767259 (80004005)':

Method 'Endaled' of object'_CommandBarButton' failed.

The macro stops at this point

Code

oCtrl.Enabled = False

in the following piece of code.

Code

'Disable all Copy menus
     For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
            oCtrl.Enabled = False
     Next oCtrl

Any ideas?

Dave Hawley · Sep 15th 2006

Re: Prevent Certain Actions Macro

Have you got any internal Workbook protection? That is, Tools>Protection>Protect Workbook.

Timbo · Sep 18th 2006

Re: Prevent Cut/Copy/Move Macro Code

Hi Dave,

Individual worksheets are protected but not the whole workbook.

Tim.

Prevent Cut/Copy/Move Macro Code

Participate now!

Share