Help with shared worksheet protection....

[Carol D'Amato]

Hi all!  Hoping I can get help with this!

We have a sheet a bunch of us here share that calculates estimated shipping costs.  Every time someone uses it, it saves the last weight and dimension entry someone made to the sheet.  How can we all use it, enter our info and have it auto clear for the next person to use once it is re-opened??  Is there a way It can LOCK the original version storing JUST the formulas but not the updates people make to the sheet??  Hope this makes sense!!  Thanks in advance!!

[#2]

Hi Carol,

You could save the file as Read only  via File > Save As > Tools > General Options >

You could also add a a password to modify

[#3]

Thank you for responding!! If we do "read only" or password modify, will people still be able to use the sheet.  It is being used for shipping calculations so there are columns where whoever needs a shipping quote can put in the weights and dimensions of the shipment and then the other columns have formulas that will generate an approx. shipping quote.  Will people still be able to do this?  We just want to ensure that when the spreadsheet is closed, when the next person opens, the prior user's weights and dims do not get saved causing potential overlap and thus miscalculation if they don't see the columns were already populated.  Thanks again :)

[#4]

I would be inclined to have it readonly. (File, Info, Protect Workbook, Always Open Read-Only). People can choose to save it but they have to consciously decide to do so. 

If that won't work you could use vba code that clears the cells that people fill in and then save the workbook again every time someone closes the workbook. 

Something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Worksheets("Sheet1").Range("B3:B4").ClearContents
    ActiveWorkbook.Save
End Sub

That way you know the relevant cells are always empty whenever the next person opens the workbook.

[#5]

Hi Carol
Yes, read-only allows users to use the file but not save it

[#6]

Thanks so much :)