Preventing moving of cells

[ElvisSteel]

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

 

[Jon von der Heyden]

Hello

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

 

[ElvisSteel]

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

 

[Jon von der Heyden]

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:

[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]

 

[ElvisSteel]

Thanks again Jon - very helpful