Disable Cut in a Specific Range

[gnod]

How to disable Cut in a specific range (A1:A10)?? :help

[mdmackillop]

No guarantees on this one, but give it a try. Post the code into the Worksheet module.

Dim LastCell As Range

Private Sub Worksheet_Activate()
Set LastCell = Selection
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If LastCell Is Nothing Then GoTo Exits
If Not Intersect(LastCell, Range("A1:A10")) Is Nothing Then
If Application.CutCopyMode = xlCut Then
Application.CutCopyMode = False
End If
End If
Exits:
Set LastCell = Target
End Sub

[gnod]

It works but isn't it possible to disable the cut in Edit->Cut, Ctrl-X, and in the context menu (right click).. the reason why i want it to disable is my formula turns to "#REF" when the user use the Cut.. please refer to the attach file.. thanks..

[lucas]

Try looking up iserror in help:



Wrap the following formula around the formula in the cell, where old_formula is the formula that was previously in the cell.
=IF(ISERROR(old_formula),"",old_formula)

[lucas]

hmm..
Seems like Malcolm's code does what your asking.....try to cut and paste out of the range in column A try it both ways.

Cut shows but you cannot perform the action to paste it.

[gnod]

Try looking up iserror in help:

but the formula in D3, =IF(ISERROR(B3),"",B3) will change to =IF(ISERROR(B4),"",B4) when the user use the Cut, which is i don't want to happen..

[gnod]

hmm..
Seems like Malcolm's code does what your asking.....try to cut and paste out of the range in column A try it both ways.

Cut shows but you cannot perform the action to paste it.
yes i know i already said


It works
but is it possible to disable it in Edit menu and others..

[lucas]

I guess I'm not following you because if I try to cut from the edit menu...it doesn't cut...well it looks like it cuts but you can't paste it anywhere..same as the right click menu.

The word cut is not disabled but you can't actually cut it....from either the right click menu, the edit menu or using Ctrl X.

did you try to cut and paste from the file I posted using all three methods?

[gnod]

yes i already tried it since malcolm provide a solution.. and it works and it solve my problem.. i'm just asking if it is possible to dimmed/disable the Cut in Edit menu and others

[lucas]

It gets involved but this will grey out Cut on the Edit menu:
Application.CommandBars("Edit").Controls("Cut").Enabled = False

[lucas]

For a discussion on the right click menu and others:
http://www.vbaexpress.com/forum/showthread.php?t=353&highlight=disable+menu

[gnod]

Thanks mdmackillop and lucas :)

i use the code of lucas to disable the "Cut" because mdmackillop's code generate error when i try to cut from B1 and paste it to C1 then Cut again from C1 and move the selection to B1

Run-time error '1004';
Method 'Intersect' of object '_Global' failed