Copy/insert copied cells when protection is on...

[BeSmart]

Hi
I have a spreadsheet with heaps of large formulas on each row.

I wanted to hide the formulas so users don't see/change/freak about them so
I used protection but ticked on all boxes allowing users to make changes but
not see the formula.

Unfortunately this doesn't seem to allow the user to copy a row and insert
copied cells to copy the formulas to a new row. It only copies and pastes
the result of the formula.

Does anyone know how I can hide formulas but allow users to copy/insert
copied cells?

 

[Dave Peterson]

You could provide a macro that would unprotect the sheet, copy|paste the
rows/formulas, then reprotect the worksheet.

David McRitchie has some code to insert rows and copy formulas at:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

(You'll have to add unprotect and reprotect stuff.)

But I think you're selling your users short. If you keep hiding stuff like
this, they may never get to learn to use excel effectively.

And since excel is used for all sorts of stuff, it might be a disservice to them
in the long run.