Inhibit Cut (allow Copy)  
Author Message
bien





PostPosted: Mon Mar 28 19:52:17 CST 2005 Top

Excel Programming >> Inhibit Cut (allow Copy)

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

Excel288  
 
 
Bernie





PostPosted: Mon Mar 28 19:52:17 CST 2005 Top

Excel Programming >> Inhibit Cut (allow Copy) 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




> 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


 
 
duncan





PostPosted: Mon Mar 28 21:15:02 CST 2005 Top

Excel Programming >> Inhibit Cut (allow Copy) 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



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


> > 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
>
>
>
 
 
Bob





PostPosted: Tue Mar 29 02:00:28 CST 2005 Top

Excel Programming >> Inhibit Cut (allow Copy) 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)




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

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


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


 
 
NickHK





PostPosted: Wed Mar 30 01:01:44 CST 2005 Top

Excel Programming >> Inhibit Cut (allow Copy) duncan,
Application.CellDragAndDrop = False

or Protection

NickHK



> 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