Protect formulas which reference to unlocked cells in protected s.

R

RJL52

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

NoodNutt

G'day RJL52 (No name supplied)

Try this

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

HTH
Mark.
 
R

RJL52

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

NoodNutt

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

NoodNutt

Ron

Apologies

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

Will try something else.

Will get back to you

Sorry

Mark.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I disable cut and paste in protected unlocked worksheet 0
Protect conditional formatting in destination cell 2
corrupted formula in protected worksheet 21
Insert Copied Rows or Drag/Drop Formulas While Protected 2
paste locked cells and unlocked cells in protected sheet 15
Copying formulas between sheets and keeping original ref 8
How do I protect cells or a range of cells in excel spreadsheet? 1
Auto fit text in linked cells of a protected worksheet 1

Top