Preventing moving of cells

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
Hi,

Is it possible to stop cells being moved, i.e. disable cut/paste and dragging of cells, but still allow copy/paste?
I have a large worksheet into which users enter data, and there are formulae that directly reference the cells. So as soon as anyone drags a cell to a new place, or uses cut/paste they get #REF! errors.
Ideally I'd like to stop them moving cells but still allow copy/paste.

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hello

This article shows you how to disable those controls and shortcuts, you should be able to adapot to restrict the cut operation only.
 
Upvote 0
Jon,

Thanks for this. It all seems to work fine in terms of disabling the use of Ctrl+X, Ctrl+Del and the options when right-clicking, but I can't get the code to disable the "Cut" option in the ribbon to be disabled. I'm wondering if the code is only applicable to versions prior to Excel 2007.

Steve
 
Upvote 0
Hi Steve

Yes this is for versions prior to XL07. I haven't experimented doing the same of the ribbon and I only have access to XL03 today. So I'm afraid I can't sugget an alternative just this minute.

Although, you can also use a SelectionChange event to disable cut in the same worksheet:

Code:
[COLOR="Blue"]Private[/COLOR] [COLOR="Blue"]Sub[/COLOR] Worksheet_SelectionChange([COLOR="Blue"]ByVal[/COLOR] Target [COLOR="Blue"]As[/COLOR] Range)
    [COLOR="Blue"]If[/COLOR] Application.CutCopyMode = xlCut [COLOR="Blue"]Then[/COLOR] Application.CutCopyMode = [COLOR="Blue"]False[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,215,467
Messages
6,124,984
Members
449,201
Latest member
Lunzwe73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top