Set Read-Only status via Macro, doesn't persist.

Hi all,

I've written a macro to password protect a worksheet and convert the workbook to Read-Only. It seems to work fine, but after the file is closed, the Read-Only status doesn't stay put. Logically this makes sense to me as the file isn't saved once the read-only status is set, but it can't be saved because it is read-only. Am I missing something simple here? Code below

Please Login or Register  to view this content.

Thanks in advance

There are two kinds of read-only. Your code indicates that the file is open in Excel in read-only mode. This is not a persistent state for the file. It is only significant while the file is open in this instance of Excel. You can try this

Please Login or Register  to view this content.

but I have not tested it.

If you want the Windows file system attribute to be read-only, then a different approach is needed.

Please Login or Register  to view this content.

Can you clarify what you need to do?

Sure, essentially what I have is a calculation spreadsheet that will have data added over the course of a week or so. Once we're done with editing it will be accessed by multiple people across departments. A small accidental edit would be near impossible to spot, so we fully protect them and make them read-only.

I'm starting with an open file in Read-Write mode which is password protected. It has some cells locked (containing formulae) and others unlocked - to allow entering of data.

The code above is attached to a macro button, which is to be pressed when all data has been entered.

The macro should then unprotect the sheet, lock all cells, re-protect the sheet, then save and convert to Read-Only.

The reason I didn't go with Save As was that the user would then have to manually delete the Read-Write version afterwards - users skipping steps is the reason for automating this process in the first place.

I'll try your second suggestion and see if that does the job. Thank you

EDIT: The second suggestion seems to have done the trick. Thanks for your help.