Protection from cut and paste

Cell C1 is protected and has the formula A1 + B1. The users in my office
sometimes cut and paste A1 and B1 to A2 and A2. After the cut and paste the
formula in C1 changes to A2 + B2. What I want is for the formula to remain
A1 + B1. Is there a way to protect or prevent cuts and pastes?

JP
Tue Mar 11 21:14:44 CDT 2008

Change the formula in C1 to:

=3D$A$1+$B$1

But if they cut and paste A1 into A2 and B1 into B2, the formula would
then =3D 0.


HTH,
JP

On Mar 11, 7:49=A0pm, Invalid <inval...@invalidINVALID.com> wrote:
> Cell C1 is protected and has the formula A1 + B1. The users in my office
> sometimes cut and paste A1 and B1 to A2 and A2. After the cut and paste th=
e
> formula in C1 changes to A2 + B2. What I want is for the formula to remain=

> A1 + B1. Is there a way to protect or prevent cuts and pastes?

Jim
Tue Mar 11 21:27:47 CDT 2008

You could right-click the sheet tab, choose View Code and paste the
following code into the large white window on the right side...
'--
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo OffTrack
If Me.Range("C1").Formula <> "=A1+B1" Then
Application.EnableEvents = False
Me.Unprotect "Invalid" '<<<ENTER CORRECT PASSWORD
Me.Range("A1:B1").Locked = False
Me.Range("C1").Formula = "=A1+B1"
Me.Protect "Invalid" '<<<ENTER CORRECT PASSWORD
End If
OffTrack:
Application.EnableEvents = True
End Sub
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Invalid"
wrote in message
Cell C1 is protected and has the formula A1 + B1. The users in my office
sometimes cut and paste A1 and B1 to A2 and A2. After the cut and paste the
formula in C1 changes to A2 + B2. What I want is for the formula to remain
A1 + B1. Is there a way to protect or prevent cuts and pastes?

Invalid
Tue Mar 11 21:32:59 CDT 2008

When I change the formula in C1 to =$A$1+$B$1 and do the cut and paste, the
formula in C1 changes to $A$2+$B$2. What I need is C1 to be the sum of A1
and B1 after the cut and paste.

I wouldn't post your real email address - search for "munging email
address"

JP <snip@snip.net> wrote in
news:c1b30389-102a-45c3-8f2a-25e23adef19b@i29g2000prf.googlegroups.com:

> Change the formula in C1 to:
>
> =$A$1+$B$1
>
> But if they cut and paste A1 into A2 and B1 into B2, the formula would
> then = 0.
>
> HTH,
> JP
>
> On Mar 11, 7:49 pm, Invalid <inval...@invalidINVALID.com> wrote:
>> Cell C1 is protected and has the formula A1 + B1. The users in my
>> office sometimes cut and paste A1 and B1 to A2 and A2. After the cut
>> and paste th
> e
>> formula in C1 changes to A2 + B2. What I want is for the formula to
>> remain
>
>> A1 + B1. Is there a way to protect or prevent cuts and pastes?
>
>

Earl
Tue Mar 11 22:37:57 CDT 2008

Invalid,

There's nothing that will stop a formula from changing when a cell to which it refers is
moved, including when it's cut/pasted. Try:

=INDIRECT("A1")+INDIRECT("B1")

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Invalid" <invalidl@invalidINVALID.com> wrote in message
news:Xns9A5F6270F2BF6rleasycom@208.49.82.28...
> Cell C1 is protected and has the formula A1 + B1. The users in my office
> sometimes cut and paste A1 and B1 to A2 and A2. After the cut and paste the
> formula in C1 changes to A2 + B2. What I want is for the formula to remain
> A1 + B1. Is there a way to protect or prevent cuts and pastes?
>

Invalid
Wed Mar 12 00:41:25 CDT 2008

Earl

INDIRECT seems to work. Never used it before. Thanks everybody.

Invalid

"Earl Kiosterud" <someone@nowhere.com> wrote in
news:#kyYlH$gIHA.1188@TK2MSFTNGP04.phx.gbl:

> Invalid,
>
> There's nothing that will stop a formula from changing when a cell to
> which it refers is moved, including when it's cut/pasted. Try:
>
> =INDIRECT("A1")+INDIRECT("B1")
>

JP
Wed Mar 12 06:06:26 CDT 2008

Thanks, but technically this is a throwaway address anyway.

--JP

On Mar 11, 10:32=A0pm, Invalid <inval...@invalidINVALID.com> wrote:
> When I change the formula in C1 to =3D$A$1+$B$1 and do the cut and paste, =
the
> formula in C1 changes to $A$2+$B$2. What I need is C1 to be the sum of A1
> and B1 after the cut and paste.
>
> I wouldn't post your real email address - search for "munging email
> address"
>