issue using formulas in a shared workbook

Question

Good morning.

I have a shared workbook for workers in an office.  The workbook is created each month from a master log.  For example, I set up a master log for December, then run the code to set up a December shared workbook for the workers in the office--each worker has his/her own worksheet in the workbook.  I want it to be shared so that workers can go into the file at any time and not have to wait for someone else to come out of the file.

In this shared workbook, I have formulas that copy over from the master log (Range("K3:K5000") on each worker sheet) to calculate the due dates for the applications they list on their individual worksheets.  I have researched finding some way to protect those formulas to prevent a worker from deleting them or overwriting them.  I have discovered that I cannot protect a shared worksheet.

Is there any way in Excel to prevent someone from deleting/overwriting those formula cells, yet keep the workbook shared?  It is not going to be feasible to share/unshare/share the workbook each time it is used because of the volume of applications we receive each day.

Thanks.

Answer 1

You can protect a shared workbook.

You must unshare first.

Then on the sheet with the formulas, select all cells and unlock them through Format>Cells>Protection.

Then select the range to lock and do so as above.

Protect the sheet in Tools>Protection>Protect Sheet

Now go to Tools>Protection>Protect and Share Workbook.

Gord