Open Workbook as Read-Only using VBA (Excel 2000>)

Topic

New Reply

How do you open a Workbook as read-only? I feel quite silly asking this Q, but Iv’e tried ActiveWorkbook.Open ReadOnly:=True in the Workbook_Open event and it debugs!!! In the help files they do not show examples of this. There is a property called .ReadOnly, but this only determines in a workbook is read-only or not!

I need the workbook to open read-only without the user needing to accept the prompt that comes from the Password to Modify option!

Tx

Reply | Quote

Replies

ActiveWorkbook.Open makes no sense – if it is the active workbook, it is already open. Once a workbook has been opened, you cannot change its read-only status (for the current session). You can open a workbook from within Excel as read-only:

Workbooks.Open FileName:=”F:ExcelExpenses.xls”, ReadOnly:=True

but of course, you cannot use that code from within the workbook Expenses.xls itself.

Reply | Quote

Hans. You are correct…and your reply makes perfect sense. I tested your code after I added it to the Personal Macro W/B, and this opens Expenses as read-only.
The problem is that I still need to run the macro myself. Is there any other way that I can have a certain workbook open dynamically as read-only. (IE, without human interaction, without a prompt.) Any property setting or code method that will force a workbook to be read-only when it opens? Any ideas!

Reply | Quote

By far the simplest way is to make the .xls file read-only in Windows Explorer, but you asked about VBA…

Reply | Quote

Just out of interest, is there a way to ‘protect’ a workbook when it is opened – and thus prevent it being changed – and setting its Saved property to True?

(I agree that either setting a file to ReadOnly in Explorer – if not the whole folder – is by far the simplest method to stop files being over-written by restricted users.)

Reply | Quote

1) You can protect a worksheet (Tools | Protection | Protect Sheet…). Only cells explicity unlocked in Format | Cells, Protection tab can be edited. You can set a password needed to unprotect the worksheet.

2) You can set a “password to modify” in the Security tab of Tools | Options. When the user opens the workbook, (s)he must either provide the password or open the workbook read-only. The user can still modify the workbook and use Save As to save it under another name.

Reply | Quote

Actually that may just be the answer. I only turned to VBA as it can Virtually Brainwash Anything in Excel!!
Thanx. I think my problem is solved!
Cheers

Reply | Quote

From within the workbook, you can do a File/SaveAs. In the SaveAs dialog box click on Tools, then on General Options. In that dialog box click on “Read-only recommended”. The WB will then display a message box at open telling the user that the WB should be opened Read-only. However, the user can override this recommendation. Another option is to right click on the file in Explorer and mark the file as Read-only. Of course, the user can also right click on the file and unmark it. Another option is to put it into a Read-only directory on a network drive. The only VBA option that I can think of would be to create a dummy workbook that the user opens. This workbook would have an open event routine that opens the real WB as Read-only and then closes itself.

Reply | Quote

>Another option is to right click on the file in Explorer and mark the file as Read-only. Of course, the user can also right click on the file and unmark it. Another option is to put it into a Read-only directory on a network drive.

FWIW it also possible to put Read-Only NTFS permissions (which can’t be readily un-checked) on the file itself.

Reply | Quote

This may be too obvious, but why not create a macro in a separate file and assign it to a button or menu or something to give the user easy access.
You can then set the attribute to ReadOnly before you open the target Workbook:
SetAttr “C:My DocumentsExpenses.xls”, vbReadOnly
Workbooks.Open Filename:=”C:My DocumentsExpenses.xls”
Include a line in the code to close the macro workbook as soon as it’s done its thing.
Any use?

Reply | Quote

Thanx Guys. As commented; There is certainly no lack of options to effectively do this. Thanx for all the replies as it is always useful to know ALL the options available. In the end we settled with marking the file as read-only in Windows Explorer. There was no need for extensive security on the file and it was did not require and coding.
Big to all!

Reply | Quote