PDA

View Full Version : Different access for different users



JJAMS
10-02-2008, 10:02 AM
I'm very new to VBA, and this is my first attempt at using code. I want to offer access to different parts of the worksheet for different users. I tried to accomplish this by setting up passwords. As far as I can tell, it works fine. The problem comes about when someone tries to modify the worksheet without entering a password. Excel automatically prompts the user to unprotect the sheet, and tells them how to do it. Any suggestions? I've attached my code below. Any help will be much appreciated.

Sub WORKBOOK_BEFORECLOSE(CANCEL As Boolean)
Call PIMPLOCK
End Sub

Sub PIMPLOCK()
ActiveSheet.Unprotect
Range("A11:L2000").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End Sub

Sub PROCESSUNLOCK()
ActiveSheet.Unprotect
Range("A11:I2000").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End Sub

Sub INSPUNLOCK()
ActiveSheet.Unprotect
Range("J11:L2000").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End Sub

Sub PIMPUNLOCK()
ActiveSheet.Unprotect
Range("A11:L2000").Select
Selection.Locked = False
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End Sub
Sub PASSWORD()
Dim PASSWORD As String
PASSWORD = InputBox("ENTER YOUR PASSWORD (PLEASE USE ALL CAPS)")
If PASSWORD = "DUDE" ThenCall PIMPUNLOCK
Else
If PASSWORD = "PROCESS" Then
Call PROCESSUNLOCK
Else
If PASSWORD = "INSP" Then
Call INSPUNLOCK
Else
MsgBox "YOU ARE NOT AUTHORIZED TO MODIFY THIS SPREADSHEET"
End If
End If
End If
End Sub

Bob Phillips
10-02-2008, 12:33 PM
Where do all of those other routines get called, all we can see is some code that locks the sheet down.

JJAMS
10-02-2008, 01:04 PM
I've attached the file itself. If I understand your question, the code would be initiated by clicking on the "unlock" button.

Bob Phillips
10-02-2008, 01:53 PM
Try this in ThisWorkbook




Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call PIMPLOCK
End Sub

Private Sub Workbook_Open()
Dim sPASSWORD As String
sPASSWORD = InputBox("ENTER YOUR PASSWORD (PLEASE USE ALL CAPS)")
Call PIMPLOCK
Select Case sPASSWORD

Case "DUDE": Call PIMPUNLOCK

Case "PROCESS": Call PROCESSUNLOCK

Case "INSP": Call INSPUNLOCK

Case Else: MsgBox "YOU ARE NOT AUTHORIZED TO MODIFY THIS SPREADSHEET"
End Select
End Sub

Private Sub PROCESSUNLOCK()
With Worksheets("Sheet1")
.Unprotect
With .Range("A11:L2000")
.Locked = False
.FormulaHidden = False
End With
.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End With
End Sub

Private Sub INSPUNLOCK()
With Worksheets("Sheet1")
.Unprotect
With .Range("J11:L2000")
.Locked = False
.FormulaHidden = False
End With
.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End With
End Sub

Private Sub PIMPUNLOCK()
With Worksheets("Sheet1")
.Unprotect
With .Range("A11:L2000")
.Locked = False
.FormulaHidden = False
End With
.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End With
End Sub

Private Sub PIMPLOCK()
With Worksheets("Sheet1")
.Unprotect
With .Range("A11:L2000")
.Locked = True
.FormulaHidden = False
End With
.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End With
End Sub

GTO
10-02-2008, 01:56 PM
You mentioned being new to vba, you may wish to review this, as mdmackillop addresses issue quite nicely. :-)

http://www.vbaexpress.com/kb/getarticle.php?kb_id=531

As to Excel "...prompting...and tells them how...", I believe you'll want to inlude a password when either un/protecting the sheet programatically.

ActiveSheet.Protect DrawingObjects:=True, CONTENTS:=True, Scenarios:=True

ActiveSheet.Protect Password:="MySuperSecretPassword", DrawingObjects:=True, CONTENTS:=True, Scenarios:=True

'//You could also declare the password as a Const if you wished.//

JJAMS
10-03-2008, 02:47 PM
Thanks, xld & GTO for your responses. I used your code, xld, and it worked great. However, once the user has logged into their area, they are still able to unprotect the document using the default Excel toolbar. Is there any way to disable, or hide, that functionality? Please let me know.

GTO
10-03-2008, 03:02 PM
Protect Password:="MySuperSecretPassword", DrawingObjects:=True, CONTENTS:=True, Scenarios:=True

Include a password that only you know when reprotecting the sheet(s).

JJAMS
10-03-2008, 03:19 PM
Maybe I'm not totallly understanding what you are trying to tell me GTO. Bear with me. This file will located in a shared location. Many users will be opening the file, gaining access to their respective area of the worksheet, adding and/or revising data, then saving the file when they are done. If they needed to have a password to unprotect the sheet, wouldn't they be prompted for that same password when the code ran and wanted to re-protect the sheet?

GTO
10-06-2008, 12:36 AM
...This file will located in a shared location. ...If they needed to have a password to unprotect the sheet, wouldn't they be prompted for that same password when the code ran and wanted to re-protect the sheet?


...I used your code, xld, and it worked great. However, once the user has logged into their area, they are still able to unprotect the document using the default Excel toolbar. Is there any way to disable, or hide, that functionality?...

JJAMS -

My apologies for either not seeing this or forgetting to answer. My input was less than stellar in clarity. What I wanted to relay was that if each '.Protect' and '.Unprotect' includes a password (that only you know), then if the user attempts to unprotect the sheet from Tools|Protection|Unprotect Sheet..., they will run into Excel asking for a password they don't know. Viola! You have decreased the user's ability to 'get around' your protection.

Here's just a few lines of the code to show what I mean. Don't forget to include the password wherever the .Protect or .Unprotect are.

Your "authorized" users will not need the password, as it is relayed to the app by the code.

Hope I made better sense that time,

Mark

Const PWORD As String = "MaryHadALittleLamb!"

Private Sub PROCESSUNLOCK()
With Worksheets("Sheet1")
.Unprotect PASSWORD:=PWORD
With .Range("A11:L2000")
.Locked = False
.FormulaHidden = False
End With
.Protect PASSWORD:=PWORD, DrawingObjects:=True, CONTENTS:=True, Scenarios:=True
End With
End Sub

JJAMS
10-06-2008, 07:58 AM
That worked great! Thank you GTO, problem solved.

GTO
10-06-2008, 01:17 PM
Glad we were able to help. :thumb

Mark