Protection from cut and paste

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Invalid - 12 Mar 2008 00:49 GMT

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?

Reply to this Message

JP - 12 Mar 2008 03:14 GMT

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

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

Reply to this Message

Invalid - 12 Mar 2008 03:32 GMT

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"

> Change the formula in C1 to:
>
[quoted text clipped - 14 lines]
>
>> A1 + B1. Is there a way to protect or prevent cuts and pastes?

Reply to this Message

JP - 12 Mar 2008 12:06 GMT

Thanks, but technically this is a throwaway address anyway.

--JP

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

Reply to this Message

Jim Cone - 12 Mar 2008 03:27 GMT

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?

Reply to this Message

Earl Kiosterud - 12 Mar 2008 04:37 GMT

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

Signature

Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

Reply to this Message

Invalid - 12 Mar 2008 06:41 GMT

Earl

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

Invalid

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

Reply to this Message

Rate this thread: