+ Reply to Thread
Results 1 to 6 of 6

Remove Dialog Box Requiring Password in Read-Only/Write Excel Document

  1. #1
    Registered User
    Join Date
    10-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Remove Dialog Box Requiring Password in Read-Only/Write Excel Document

    I have created an Excel 2013 workbook that users can modify if they know the password, or can view as read only. However, when anyone opens the file, they first see this dialog box asking them to either choose Read-Only or to enter the password.

    Dialog Box.png

    Is there any available VBA code that will skip this dialog box and automatically open the workbook as read-only, such that the user doesn't need to face any dialog boxes and can get straight to the Workbook as usual? The dialog box I am talking about is titled "Password" and states "{Document Name} is reserved by {Author} Enter password for write access, or open read only." The dialog box appears on the default gray Excel background and is a rather uninviting window if I need to share this workbook with a client. I'd like to move past this and get straight to the contents of the Workbook as read-only.


    I have seen the following bits of code. Turning off alerts does not work, while the first block of code will automatically ensure the Workbook is always read-only, but still does not remove the dialog box:


    Private Sub Workbook_Open()
    If Not ActiveWorkbook.ReadOnly Then _
    ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
    End Sub

    (From this thread: https://www.mrexcel.com/forum/excel-...essagebox.html)

    Application.DisplayAlerts = False

    (From this thread: https://stackoverflow.com/questions/...n-as-read-only)

    Neither of these are what I'm looking for.

    Thanks for your help!
    Last edited by GBFoxx; 10-16-2017 at 11:44 AM.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Remove Dialog Box Requiring Password in Read-Only/Write Excel Document

    Hi,

    I suspect any code will only run after the dialog has appeared so I don't think there is any way to suppress that.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    10-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Re: Remove Dialog Box Requiring Password in Read-Only/Write Excel Document

    Quote Originally Posted by xlnitwit View Post
    Hi,

    I suspect any code will only run after the dialog has appeared so I don't think there is any way to suppress that.
    My thought too, unfortunately
    I guess instructing people not to freak out when they see a pop-up window is the easier solution...

    I'll keep taking solutions, of course.
    Thanks.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Remove Dialog Box Requiring Password in Read-Only/Write Excel Document

    You could perhaps protect the sheets/structure instead of making it read-only?

  5. #5
    Registered User
    Join Date
    10-16-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    3

    Re: Remove Dialog Box Requiring Password in Read-Only/Write Excel Document

    Quote Originally Posted by xlnitwit View Post
    You could perhaps protect the sheets/structure instead of making it read-only?
    I've considered this. However, our group is trying to restrict any access the customer has to the data, such that they can only use the dashboard tool we are creating and not see the underlying raw data contained on another tab within the report. When protecting a sheet, I see there are many options such as "Format Cells", "Delete columns" etc etc. To my knowledge, even if we protect the sheets, there's no option that prevents them from copying the data and pasting it elsewhere.

    I've made the sheet "very hidden" as well in VBA but that can be undone if the workbook isn't Read-Only; which is why I'd like to make it Read-Only. Of course, an agreement not to steal data is the easiest method to prevent this, but that can be tough/take time to get through in our group.

    Thanks!
    Last edited by GBFoxx; 10-16-2017 at 12:13 PM.

  6. #6
    Registered User
    Join Date
    09-12-2019
    Location
    London
    MS-Off Ver
    Office16
    Posts
    1

    Re: Remove Dialog Box Requiring Password in Read-Only/Write Excel Document

    If you are opening the file with

    Workbooks.Open Filename:=myfile, ReadOnly:=True

    this should work, its working for me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Applying password to read only document
    By Yossarian17 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2013, 11:55 AM
  2. Replies: 0
    Last Post: 06-11-2013, 05:14 PM
  3. Replies: 1
    Last Post: 03-11-2013, 05:44 PM
  4. [SOLVED] COmmand button requiring password, if password ok, unhide sheet.
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2012, 09:43 AM
  5. Replies: 0
    Last Post: 11-15-2010, 10:41 AM
  6. [SOLVED] Why can't I write in a document that is not read only?
    By DLD in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-18-2005, 06:34 PM
  7. Replies: 6
    Last Post: 03-24-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1