[RESOLVED] Workbook Open - Pasword protect 2 levels

[lionelnz]

Hi there.

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

Here is the code

Private Sub Workbook_Open()
 
Dim Message, Title, Default, Password As String
Message = "Enter your password" ' Set prompt.
Title = "Password" ' Set title.
Default = " " ' Set default.
' Display message, title, and default value.
Password = InputBox(Message, Title, Default)
 
If Password = "test" Then
Workbooks.ReadOnly = True
Else
Workbooks.ReadOnly = False
End If
 
End Sub

an error occurs on "Workbooks.ReadOnly = True" line.

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

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

Cheers

Lionel DownUnda

[RobDog888]

The Open method of the workbooks collection has a Password property so no need to handle it all yourself.

Workbooks.ReadOnly error because no workbook is open yet.

[lionelnz]

Thanks for the quick reply.

If you are referring to 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 & management such as myself need to be able 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 only one password is entered and depending on the password gives the user fullaccess or readonly. This will also save management having to remember different passwords for readonly access to different staff files.

Lionel downunda

[RobDog888]

Why would you need to enter in 2 pwds? With the Open function you only supply the pwd once. If you dont have it then it can be opened as read only. Once the workbook is saved with a pwd, it is only needed again to open it on the next open.

[lionelnz]

If you dont have it then it can be opened as read only. Once the workbook is saved with a pwd, it is only needed again to open it on the next open.

And that is the problem. The purpose of the workbook is a timesheet for about 10 staff. The wkbk is saved to a very public folder and some staff are sensitive about who sees their timesheets. (the workbook will have timesheets for 12 months set up with one month being in one worksheet so their wil be 12 wksheets at least for the work book.)

Therefore I need one password that will let managemnet read only (only mmt will have that password) & the individual staff wil have their own password which will give access to their own timesheet workbboks.

I have been thinking about a creative solution & here is rough psuedocode

Workbook has all but one insignificant sheet XlVeryHidden
On workbook open event
Enter password
if password = "readonly" then
unhide appropriate sheets
make sheets readonly
Else password = "read/write access"
unhide appropriate sheets
make sheets read/write access
End if

If I have XLVeryhidden when opening the workbook, I wouldn't need Before_save event to make sheets XLVeryHidden again should I?

Do you think this would work?

Key points to remember are that it is a simple spreadsheet that does not hold overly sensitive data and that staff are not overly PC Literate.

Your feedback is appreciated.

[lionelnz]

There is one workbook with 12 sheets (one for every month).

I want to be able to have one password only that a user enters and depending on the password gives the user read/write access OR read only.

The key is that the workbook has to be unavailble (protected) unless the user enters the right password for a) full access or b) read only.

I want only one password to be used at any time and this is why the "save as" option is not useful for the end users in my case.

Cheers anyway

[Siddharth Rout]

Okay

Here is a general idea on how you can go about it. i can think of two ways

WAY 1) When a user opens the workbook he/she is prompted for a password. Lets say the user enters "admin" or "guestpass" for a password depending on the password we can assign the user read/write or read only access

For this method we will use 13 sheets in a workbook. 12 for months and 1 for hiding the password data.

In sheet1
In cell A1 type USER and B1 type PASSWORD
In cell A2 type ADMINISTRATOR and B2 type admin
In cell A3 type GUEST and B3 type guestpass

Now create a form and insert three controls. A Combobox, A textbox and 1 commandbutton.

Set the combobox rowsource property to sheet1!A2:A3
Set the textbox password char to '*'

Now rest is writing a code for password checking which i am sure you can do that. compare the values of textbox with the range("B2").value or range("B3").value

In the workbook open event make the userform visible

WAY 2) This is the 2nd way. see the file attached

Hope I have understood you correctly and it helps...

Note: I have just shown you the basic approach. The coding part is yours

[Edit 25/03/2007] : Since the post is resolved.. deleting the attachment as my total attachements have crossed the allowed limit

[lionelnz]

Thanks for this. I certainly like the idea of a userform. I needed something by tomorrow & so this is what I have for now.
Can I use masking for user password input box in VBA (without userform)?
If so how?

Private Sub Workbook_Open()
'Provide 3 level protection
'by using only one password
 
Dim Password As String
Dim sh As Worksheet
Worksheets("Sheet1").Activate
'Protect & hide all but 1st wksht
For Each sh In Worksheets
    If (sh.Name <> "Sheet1") Then
    sh.Protect
    sh.Visible = xlSheetVeryHidden
    End If
Next sh
 
'Ask for user password
Password = InputBox("Password")
 
 
'Tutor password unprotect sheets & unlock cells
 
    If (Password = "t1") Then
    For Each sh In Worksheets
        If (sh.Name <> "Sheet1") Then
            sh.Visible = xlSheetVisible
            sh.Unprotect
            sh.Range("A3:C4").Locked = False
            sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            sh.EnableSelection = xlUnlockedCells
        End If
      Next sh
            
'Management pwrd to read only
    ElseIf (Password = "t2") Then
        For Each sh In Worksheets
            If (sh.Name <> "Sheet1") Then
                sh.Visible = xlSheetVisible
            End If
            sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
            sh.EnableSelection = xlNoSelection
        Next sh
                
'Supervisor/developer pwrd to maintain wkbk
    ElseIf (Password = "s") Then
        For Each sh In Worksheets
            sh.Visible = xlSheetVisible
            sh.Unprotect
        Next sh
    End If
End Sub

If you can suggest any code tidying up I would be grateful.