[RESOLVED] Workbook Open - Pasword protect 2 levels
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
VB 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
Re: Workbook Open - Pasword protect 2 levels
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.
Re: Workbook Open - Pasword protect 2 levels
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
Re: Workbook Open - Pasword protect 2 levels
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.
Re: Workbook Open - Pasword protect 2 levels
Quote:
Originally Posted by RobDog888
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.
Re: Workbook Open - Pasword protect 2 levels
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
Re: Workbook Open - Pasword protect 2 levels
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
Re: Workbook Open - Pasword protect 2 levels
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?
VB Code:
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.