Help w/Read Only

[KGee]

I have a monthly report file which I lock down and leave only a range of cells open. Staff make their updates and then click a "submit" button when they're done to indicate everything is complete. At this point, I want to put the file in a read-only status.

I used the built-in save as function to record the steps but want to know if there are any alternatives. I don't like having the read only box at start up, then another prompt when someone tries to save the file.

Is it possible to have my macro write another piece of code when the original "submit" button is clicked? I came across the following thread I would like to try but only want to use this piece of code after the file has originally been submitted. Can I have my macro write this code to the file once it has been submitted?
http://www.mrexcel.com/forum/showthread.php?t=372690&highlight=read+only

I'm also wondering if there are any other alternatives that I could use.

Thanks - Kevin

 

[KGee]

I guess I don't understand how this method is supposed to work as I'm not getting the results I expected. The file remains read only until it is closed. When the file is re-opened it is no longer read-only.

ActiveWorkbook.Protect Structure:=True
ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
Application.DisplayAlerts = True

How can I change my code so the file remains read only? I don't want anyone to have the ability to save any changes without supplying the password after the file has been locked. And can I supply the password as an arugment somehow?

 

[Ragnar1211]

Try using saveAs instead:

ActiveWorkbook.SaveAs Filename:= "Testing2" , FileFormat:=xlNormal, Password:="", WriteResPassword:="gerry", ReadOnlyRecommended:=False, CreateBackup:=False

 

[KGee]

Is there anyway to do it w/o a save as? If not I'll try save as and use the same name. I just want everything to be transparent for the user.

Thanks

 

[Ragnar1211]

I couldn't find a way to make it work without SaveAs. But it should still be transparent to the user with alerts off.

 

[KGee]

Forgot to say thanks, this is working fine.

Kevin

 

[Ragnar1211]

Glad to hear, will add if I ever find a way to do it without saveas.