Workbook Password Protect 2 Levels

  • Hi there.


    On opening a workbook (XL2003) I want users to enter a password
    which will give them read Only permissions or access to the whole
    book.


    I do not want use the "save as" option it is not suitable for my needs & here is why.


    I have about 10 staff who need to fill in timesheets using XL SS on a public folder on the server. The staff need full access to file & management such as myself only need to view (read only) the timesheet without the ability to change data. Using the "save as" function the staff need to input 2 passwords.


    The staff (in general) are not overly computer literate so I was hoping to be able to write code so that on Workbook_Open event only one password is entered and depending on the password gives the user full access or readonly access. This will also save management having to remember different passwords for read only access to different staff timesheet files & will give crude protection to the files.


    Here is the code




    Error occur on "Workbook.ReadOnly" lines:confused: .


    I also want to put an errorchecking code for invalid passwords.


    Again, I know I can use the "save as" option but that is not what I need.


    Cheers


    Lionel DownUnda

  • Re: Workbook Password Protect 2 Levels With 1 Pwrd


    Hi Lionel,


    I know you say you don't want to use the Save As, but if you use Save As and tick the Read Only Recommended checkbox, then you only have to put in one password, if you want access to it, if not just click the Read Only button.


    Bill

  • Re: Workbook Password Protect 2 Levels With 1 Pwrd


    Hi,


    I believe that you cannot change the attribute of a file while it is opened. If I am not wrong, the VBA line "Workbook.ReadOnly" is used to test whether a workbook is set as Read-Only before it is opened.


    I suggested using the following:


    This code disables saving changes to the workbook, but it will make a copy of the workbook and prompts you to save under another name. I do not know how to disable Excel from automatically making a copy of the workbook though.


    Maybe another person will suggest a better way to do it?

  • Re: Workbook Password Protect 2 Levels


    Would something like this work in the workbook module?


    Code
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        ActiveWorkbook.WritePassword = "test"
        ActiveWorkbook.ReadOnlyRecommended = True
    End Sub
  • Re: Workbook Password Protect 2 Levels With 1 Pwrd


    Quote from Ranger

    Hi Lionel,


    I know you say you don't want to use the Save As, but if you use Save As and tick the Read Only Recommended checkbox, then you only have to put in one password, if you want access to it, if not just click the Read Only button.


    Bill


    The problem is Bill that the files will be stored in a common folder on the server and staff do not want others reading their timesheets. By using the Save as option there has to be one password to open & view the file then a second password change data.


    I have to setup passwords, store them & then distribute them correctly to all staff & management so if I can develop (with a lot of help from others) VBA code so that staff only have one code to give them write/save access & management read only access it will make a life a lot easier.


    Cheers anyway as I have code to try out.

  • Re: Workbook Password Protect 2 Levels


    Thanks for your help but having tried various combinations I still cannot get the workbook to open or readonly with one password. Psuedo code of what I am trying to do.


    Workbook is completely protected (no read or write access)
    On workbook open
    Ask for password
    If password = "readonly" pwrd then open as readonly
    If password = "write access" then open with read/write access.


    I want any user to only have to use one password to get approproiate workbook access.


    Thanks

  • Re: Workbook Password Protect 2 Levels


    This is what I have to try an open a workbook with one password only (but different passwords giving accesss to different sheets).


    What is happening is that I usually need "s" to open workbook which will give access to Sheet1 with the inputbox asking for password.


    Now it doesn't matter which password is given but both sheets (2 & 3) become unhidden.


    Any help on this would be appreciated.



  • Re: Workbook Password Protect 2 Levels


    You seem to be asking a new question, yes? Why not start another thread so that you can get the appropriate answers?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!