Protection from cut and paste

[Invalid]

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]

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

 

[Jim Cone]

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]

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"

 

[Earl Kiosterud]

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

 

[Invalid]

Earl

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

Invalid

 

[JP]

Thanks, but technically this is a throwaway address anyway.

--JP