Protect formulas which reference to unlocked cells in protected s.
 

My spreadsheet workbook has protected sheets with some cells unlocked so that
other users can enter and edit information. I have protected formulas hidden
in other parts of the worksheets which refer to these cells as absolute
references. Everything works fine except when "cut" and "paste" are performed
on the unlocked cells, resulting in corrupted formulas. Obviously the simple
solution is to tell all users not to perform "cut" and "paste".
Frustratingly, this has not stopped it happening.
Is there a way to prevent the formulas from being corrupted when "cut" and
"paste" are performed?
From reading related discussions I suspect it requires VBASIC code, about
which I know very little.

Protect formulas which reference to unlocked cells in protected s.
 

G'day RJL52 (No name supplied)

Try this

With Application
.CopyObjectsWithCells = False
.DisplayPasteOptions = False
.DisplayInsertOptions = False
End With

HTH
Mark.

Protect formulas which reference to unlocked cells in protecte
 

Thanks for the suggestion Mark. I know very little about VisBasic command
code. I'm not sure exactly where to enter the code you have suggested. (Tried
a couple of options without success).
Could I trouble you for detail in this regard?

Ron.

"NoodNutt" wrote:

G'day RJL52 (No name supplied)

Try this

With Application
.CopyObjectsWithCells = False
.DisplayPasteOptions = False
.DisplayInsertOptions = False
End With

HTH
Mark.

Protect formulas which reference to unlocked cells in protecte
 

G'day Ron

Press ALT-F11

This will bring up Visual Basic Editor

In the Left pane,

..In bold lettering you will see VBAProject(yourworkbookname)
.. Under that you will see Microsoft Excel Objects folder
.. Double click on "This Workbook"

In the Right pane at the top of the white space you will notice 2 dropdown
boxes.

.. The left Dropdown - Select "Workbook"

.. The right Dropdown should change to "Open"
(if it doesn't, then select it manually.)

This will appear in the white space

Private Sub Workbook_Open()

End Sub

This is where you insert the code, it will look like this

Private Sub Workbook_Open()
With Application
.CopyObjectsWithCells = False
.DisplayPasteOptions = False
.DisplayInsertOptions = False
End With
End Sub

Now, along the menu ribbon, you will see "Debug"

From the dropdown ribbon select "Compile"

Save it

Then test it by closing the workbook & re-opening it.

Good luck
HTH
Mark.

Protect formulas which reference to unlocked cells in protecte
 

Ron

Apologies

Should have tested this before posting, just tested and failed.

Will try something else.

Will get back to you

Sorry

Mark.

Protect formulas which reference to unlocked cells in protecte
 

Mark

I appreciate your help and time.

Thankyou in advance.

Ron

"NoodNutt" wrote:

Ron

Apologies

Should have tested this before posting, just tested and failed.

Will try something else.

Will get back to you

Sorry

Mark.