Open workbook as read-only

[KGee]

I have a file that will be saved to a common shared drive where a slew of other files are saved. Some of the staff who have access to the share will only need read access to this particular file and creating separate folders with varying levels of read-write access will be too difficult. Is there a way for me to set a workbook to read-only mode while it's being opened, from say the Workbook Open event? I want to include a hidden sheet that contains a list of users who should have edit access and have it checked to determine whether the user should have read or edit rights when the file is opened.

The file is currently saved in a restricted share and has code in the open/close events to confirm the users ID or prompt for a password and hide certain sheets from view if they ar enot authorized.

 

[Ruddles]

The most robust way would be to save it with a 'password to modify'.

Alternatively you could test whether it was opened as read-only and if it wasn't, close it and re-open it as read-only programatically. The only problem is that as soon as you close it the code stops running, so you have to keep the workbook containing the code open whilst you do that.

Try the following code in your workbook's ThisWorkbook module:-

[FONT=Courier New]Option Explicit[/FONT]
 
[FONT=Courier New]Private Sub Workbook_Open()[/FONT]
 
[FONT=Courier New] Dim WhoAmI As String[/FONT]
 
[FONT=Courier New] If Not ThisWorkbook.ReadOnly Then    [/FONT]
[FONT=Courier New]   MsgBox "Opened as read-write"[/FONT]
[FONT=Courier New]   [COLOR=green]' remember who I am so I can re-open me later[/COLOR][/FONT]
[FONT=Courier New]   WhoAmI = ThisWorkbook.FullName[/FONT]
[FONT=Courier New] [COLOR=green]  ' kill temp file if it exists[/COLOR][/FONT]
[FONT=Courier New]   If Dir(Environ("temp") & "\temp.xls") <> "" Then Kill Environ("temp") & "\temp.xls"[/FONT]
[FONT=Courier New]   [COLOR=green]' save workbook containing running code with temp name so we can re-use actual workbook's real name[/COLOR][/FONT]
[FONT=Courier New]   ThisWorkbook.SaveAs Environ("temp") & "\temp.xls"    [/FONT]
[FONT=Courier New][FONT=Courier New][COLOR=green]   ' re-open actual workbook as read-only[/COLOR][/FONT]
[/FONT][FONT=Courier New]   Workbooks.Open WhoAmI, ReadOnly:=True[/FONT]
[FONT=Courier New][COLOR=#008000]   ' close temp workbook, thereby ending code execution[/COLOR][/FONT]
[FONT=Courier New]   Workbooks("temp.xls").Close savechanges:=False [/FONT]
[FONT=Courier New] End If[/FONT]
 
[FONT=Courier New] MsgBox "Opened as read-only"[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

The MsgBox commands are only there to show you what's going on whilst you satisfy yourself that the code works: remove them in the final version - or you could use one or both of them to give the user some information.

Of course this all relies on macros being enabled, whereas the password suggestion does not.

 

[KGee]

Hi Ruddles,

The report must be "completed" at some point by one of the designated reviewers at which time I lock the file down and save it as read-only. The problem is the report is accessed and reviewed by staff in higher level management positions and I don't want to create an extra step of having to input a password if I don't need to. I'm out of the office today so I will try your suggestion on Monday.

Thanks and have a great weekend.