Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Regards,
    Rudi

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

    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.

  4. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!
    Regards,
    Rudi

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

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

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

  6. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook as Read-Only using VBA (Excel 20

    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.)

  7. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Actually that may just be the answer. I only turned to VBA as it can Virtually Brainwash Anything in Excel!! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Thanx. I think my problem is solved!
    Cheers
    Regards,
    Rudi

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Open Workbook as Read-Only using VBA (Excel 20

    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.

  9. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Legare Coleman

  10. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Workbook as Read-Only using VBA (Excel 20

    >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.
    Grüße

  11. #10
    Lounger
    Join Date
    Feb 2001
    Location
    Toronto, Ontario, Canada
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  12. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> to all!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •