Inhibit Cut (allow Copy)

[Guest]

I'm looking for a way to prevent the user from using CUT (including the
equivalent northwest-arrow drag functionality) but not prevent copy/paste or
autofill. Users are dragging cells around and destroying associated formulas:
Absolute references don't sem to help this.
For instance, I have a formula =IF(Z15=0,0,A15/Z15). If the user drags a CUT
cell over A15, I get =IF(Z15=0,0,#REF!/Z15). Copying doesn't hurt at all. The
cell with this formula resides in, say, A147. I can make all the references
absolute - it doesn't help. How can I keep this from happening?

duncan

 

[Bernie Deitrick]

Duncan,

You can use INDIRECT to make your formula immune to the slings and arrows of
CUT and drag, etc.

=IF(INDIRECT("Z15")=0,0,INDIRECT("A15")/INDIRECT("Z15"))

HTH,
Bernie
MS Excel MVP

 

[Guest]

Actually, I tried that, and got some very unexpected results. The cells
containing the new INDIRECT reference now came up with a #REF as a cell Value
in even more cases than before. True, the actual formula in the cell remained
intact, but the Cell Values seemed to work even worse. I found, for instance,
that simply entering a value in the cell in A15 caused the formula'd cell to
come up #Ref. When I saw INDIRECT, I thought that it would be the perfect
solution, and converted all the cell formulas using a bit of string
manipulation.
<Buzzer sounds>

Any ideas why?

duncan

 

[Bob Phillips]

Post some details of the formula and the data, and we might be able to help.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[NickHK]

duncan,
Application.CellDragAndDrop = False

or Protection

NickHK