?
Solved

How do I easily password protect a worksheet and allow user to modify password?

Posted on 2011-04-19
31
Medium Priority
?
435 Views
Last Modified: 2012-05-11
I'm looking for a way to password protect several sheets in a workbook.  I've gotten a document that does exactly what I want, but I coded the passwords in the vba.  Now, I'm asked to make it easy for an admin to modify the various passwords.

I've found tons of stuff that after supplying a password, certain sheets are visible.  But, what I'm having trouble with is how to allow the document to 'reprotect' a sheet based off a password on a stored sheet.  

This way, if the Admin opens the document with nothing protected, then makes changes to the passwords for various sheets, the document re-protects the sheets using the new passwords.
0
Comment
Question by:John Desselle
  • 17
  • 13
31 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35427860
when protecting the sheets, specify the password cell:

activesheet.protect password:=sheets("admin").range("a1").value

for example.
0
 

Author Comment

by:John Desselle
ID: 35429108
I got that to work protecting just one worksheet.  What if I have multiple worksheets?  I want to find the worksheet name on the 'admin' sheet and use the corresponding password to protect that sheet.

UserName      Password      Sheet
Reg1      1234      User 1
Reg2      5432      User 2

Here is what is on the 'admin' sheet.  When closing the workbook, I want sheet 'User 1' to be protected with the password 1234 and sheet 'User 2' protected with 5432.

I'll have about 15 Regions with their own sheets and the Regional Manager needs to be able to modify their region's sheet password.

Is this making sense?
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 35429310
Here is a macro that uses a table in worksheet admin (cells A1:C15) and protects the worksheets accordingly.
Sub Protector()
Dim rg As Range, rw As Range
Dim ws As Worksheet
Set rg = Worksheets("admin").Range("A1:C15")    'Table with user, password, worksheet name
On Error Resume Next
For Each rw In rg.Rows
    If rw.Cells(1, 3) <> "" Then
        Set ws = Worksheets(rw.Cells(1, 3).Value)
        ws.Protect Password:=rw.Cells(1, 2).Value
    End If
Next
On Error GoTo 0
End Sub

Open in new window


Brad
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:John Desselle
ID: 35429377
Wow, this works as described.  Can I check to see if I understand it?
This tells where the passwords are: Set rg = Worksheets("admin").Range("A1:C15")
For each row, 1-15:  For Each rw In rg.Rows
If the sheet name cell is not empty: If rw.Cells(1, 3) <> "" Then
set focus to: Set ws = Worksheets(rw.Cells(1, 3).Value)
and protect with corresponding pw: ws.Protect Password:=rw.Cells(1, 2).Value
    End If
0
 

Author Comment

by:John Desselle
ID: 35429408
I just tested it out, and broke it.  Say an 'Admin' user opens the workbook and changes on of the sheets passwords.  It does not change the password protecting a sheet if the password is already there.  Make sense?
0
 
LVL 81

Expert Comment

by:byundt
ID: 35429429
The word "focus" has a specific (and different) meaning in VBA. To avoid confusion, I suggest using different words to explain the statement Set ws = ...

On Error Resume Next          'Turn off error handling. This step is necessary if the user changes the name of a worksheet (so name in column C doesn't match)
Set ws = Worksheets(rw.Cells(1, 3).Value)          'Assign the worksheet named in column C to a worksheet variable ws
On Error GoTo 0                    'Turn error handling back on

Thinking about a user changing the name of a worksheet, you will also want to reinitialize the worksheet variable ws with each row. If you don't if will retain its previous value and the wrong password will get assigned to it.
Sub Protector()
Dim rg As Range, rw As Range
Dim ws As Worksheet
Set rg = Worksheets("admin").Range("A1:C15")    'Table with user, password, worksheet name
On Error Resume Next
For Each rw In rg.Rows
    If rw.Cells(1, 3) <> "" Then
        Set ws = Nothing           'Reinitialize the variable (remove any association with a previous worksheet)
        Set ws = Worksheets(rw.Cells(1, 3).Value)
        ws.Protect Password:=rw.Cells(1, 2).Value
    End If
Next
On Error GoTo 0
End Sub

Open in new window


Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 35429556
You will need to train the user not to change the password.

Alternatively, you can test the password in VBA when the worksheet is activated. Note that this code can be defeated by not enabling macros when the workbook is opened. There is code to force the user to enable macros, but it adds to the complexity.
'This sub must be installed in the ThisWorkbook code pane. It won't work at all if installed anywhere else.
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ws As Worksheet
Dim rg As Range
Dim s As String
Dim v As Variant
Set rg = Worksheets("admin").Range("A1:C15")
Set ws = ActiveSheet
v = Application.Match(ws.Name, rg.Columns(3), 0)
Application.EnableEvents = False
Sh.Activate
If Not IsError(v) Then
    s = InputBox("What is the password for your worksheet?")
    If rg.Cells(v, 2) = s Then ws.Activate
Else
    ws.Activate     'Can jump immediately to any worksheet not on the list
End If
Application.EnableEvents = True
End Sub

Open in new window

0
 

Author Comment

by:John Desselle
ID: 35429576
I'll take a look in a second, it might be tomorrow.

I was going a totally different method than this.  I figured out how to capture a change and paste an old value onto another sheet.  I was then going to try to use the old value to unlock sheet before it locked with new password.

I'll take a look at your code first.

Here is what I was looking at:

 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Worksheets("Sheet2").Range("A1").Insert shift:=xlDown

    Worksheets("Sheet2").Range("A1") = vl
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    vl = Target
End Sub

Open in new window

0
 

Author Comment

by:John Desselle
ID: 35432959
Hey there Brad, I'm back.  I got your code to work perfectly for protecting the different sheets with different passwords.

And, I've gotten it to open back up with no protection.  Now I need to have an InputBox ask for password and only unprotect the sheet corresponding with that password.

I figured out that I really don't need to worry about if an Admin user changes a password on the 'admin' sheet.  When the Admin user supplies their password, all sheets will be unprotected and his change will take place.  After that, when document is closed, it will protect using all fresh passwords.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35433061
Going back to your original approach, it is possible to "break" the password if the user changes it. This can be done by a macro when the workbook opens up. There will be a delay of a minute or so for each password that was changed by a user. During this delay, the macro is breaking the changed password and restoring the one from the admin worksheet. I imagine that it would only be the administrator that faces such a delay, because the user will remember the changed password.

Experts-Exchange policy vacillates on whether or not to post the code for breaking passwords. Right now, the policy is not to post such code on the site (although it has been done so many times in the past), but rather to refer people to other sites. Since the question has already been closed--why don't you send me an email if you want to take that approach. My address is in my member profile.

Brad
0
 

Author Comment

by:John Desselle
ID: 35433123
Thanks for the response.  I don't need the code mentioned.  

A normal user with access to only their sheet will not even be able to see the 'admin' sheet, so they won't be able to 'break' the passwords.  I will set up protection for the VBA code once I'm finished working with it and only give that password to the Admin.  And, if the Admin opens the workbook with their password, all sheets should be unprotected, so if they modify the 'admin' sheet w/ passwords, it should be ok.

I need to figure out how to ask for password and only unprotect that sheet.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35433300
The Workbook_SheetDeactivate sub posted in a previous comment displays an input box asking for the password. The worksheet will only be displayed if the password is entered correctly. You could easily add a statement unprotecting that worksheet to the If statement in line 14 of that sub.

If you still aren't sure how to proceed, please post the code that you intend to use. I'll modify it accordingly.

Brad
0
 

Author Comment

by:John Desselle
ID: 35433462
I did try the code you mentioned using the SheetDeactivate, but I'd rather not make the users enter a password everytime they change sheets.

Below is the code I've put in the Workbook_Open section.  Notice the commented out lines, that's where I'm having a bit of trouble.

I've also attached the file.

Thank you so much for taking the time helping me out with this.  I really appreciate it.

 
Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet
Dim rg As Range, rw As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
Set wsSplash = Worksheets("Splash screen")
    wsSplash.Visible = xlSheetVisible
    wsSplash.Activate
    
'Inputbox asking for Password
'if password = any of the Admin passwords, then just unprotect everything
'if it <> admin passwords, only unprotect the corresponding sheet.






Set rg = Worksheets("UserInfo").Range("A1:C19")    'Table with user, password, worksheet name
On Error Resume Next
For Each rw In rg.Rows
    If rw.Cells(1, 3) <> "" Then
        Set ws = Nothing           'Reinitialize the variable (remove any association with a previous worksheet)
        Set ws = Worksheets(rw.Cells(1, 3).Value)
        ws.Unprotect Password:=rw.Cells(1, 2).Value
    End If
Next
On Error GoTo 0

    Application.EnableEvents = True
    Application.ScreenUpdating = True


End Sub

Open in new window

2011-ESF-6-Staff-Roster-PWSheet.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 35433887
Try modifying your Workbook_Open like this:
Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet
Dim rg As Range, rw As Range
Dim sPassword As String, sUser As String, sWorksheet As String
Dim v As Variant
    
    
Set rg = Worksheets("UserInfo").Range("A1:C19")    'Table with user, password, worksheet name
Set wsSplash = Worksheets("Splash screen")
    wsSplash.Visible = xlSheetVisible
    wsSplash.Activate
    
'Inputbox asking for Password
'if password = any of the Admin passwords, then just unprotect everything
'if it <> admin passwords, only unprotect the corresponding sheet.
Do
    sPassword = InputBox("Please enter your password for this workbook")
    If sPassword = "" Then Exit Sub
    On Error Resume Next
    v = Application.Match(sPassword, rg.Columns(2), 0)
    If IsError(v) Then v = Application.Match(Val(sPassword), rg.Columns(2), 0)
    On Error GoTo 0
    If IsNumeric(v) Then Exit Do
Loop
sUser = rg.Cells(v, 1)
sWorksheet = rg.Cells(v, 3)

Application.EnableEvents = False
Application.ScreenUpdating = False


On Error Resume Next
If sUser = "ADMIN" Then
    For Each rw In rg.Rows
        If rw.Cells(1, 3) <> "" Then
            Set ws = Nothing           'Reinitialize the variable (remove any association with a previous worksheet)
            Set ws = Worksheets(rw.Cells(1, 3).Value)
            ws.Unprotect Password:=rw.Cells(1, 2).Value
        End If
    Next
Else
    Worksheets(sWorksheet).Unprotect Password:=sPassword
End If
On Error GoTo 0
Worksheets(sWorksheet).Activate

Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

Open in new window


It appears you are trying to force the user to enable macros. If so, be aware that you probably want to trap the Save event and make your splash screen active. Trapping the BeforeClose event is sufficient only if you always save the workbook upon closing it. I've posted a sample workbook showing how I implement that feature. This workbook was used in a webinar I gave last month on Excel. http://www.youtube.com/user/expertsexchangetv#p/u/0/m1HMXoX1jmE

Brad
VBA-sample-workbook.xls
0
 

Author Comment

by:John Desselle
ID: 35434171
The modifications you gave work perfectly!  Awesome.  And the VBA-sample-workbook document is pretty snazzy.  I have saved it for a look later.

I'm not sure what you mean by 'trap the Save event'  I thought the below code activated the Splash screen before the file was closed.
 
Private Sub workbook_beforeclose(Cancel As Boolean)
Dim ws As Worksheet, wsSplash As Worksheet
Dim rg As Range, rw As Range
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
Set wsSplash = Worksheets("Splash screen")
    wsSplash.Visible = xlSheetVisible
    wsSplash.Activate

Open in new window


Would it be better if I put that piece of code in the Before Save section?  Or should it be in both?  The document seems to ask if you want to save every time it is closed.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35434475
When you close the workbook, you are just deleting it from RAM. Nothing is saved. So activating the splash screen as part of a BeforeClose event sub accomplishes nothing. When you reopen the workbook, the worksheet that was active when it was last saved will still be active. This is a problem if the next user chooses not to enable macros.

While you could save the workbook as part of closing it, such behavior is not expected by the user. They believe that if they royally screw up the workbook with boneheaded mistakes, all they have to do is to close it without saving and all will be well with the world.

For the above reason, I suggest making the splash screen active as part of saving the workbook. You trap the save event, hide all the worksheets except the splash screen, save the file, then restore all the worksheets. If you do it that way, a user who doesn't enable macros won't be able to see anything except for the splash screen.

You trap the save event using a Workbook_BeforeSave sub. If you take a look at the VBA sample workbook, the ThisWorkbook code pane shows the code required.

Brad
0
 

Author Comment

by:John Desselle
ID: 35434745
I believe I have taken out any save events from the beforeclose event.

I've changed things so that only the Splash screen is visible right before document closes, so that will be the only screen visible when it's opened again.  My Workbook_Open even loops through again making only the Splash Screen visible when it is first opened.  So if the user disables macros, just the Splash Screen telling them to close/reopen document is the only thing visible.

If the user enters an incorrect password, it just stays at the Enter Password message until they either close the document or hit Cancel, which if they do that, only the Splash Screen will be there.

Again, thanks for all the awesome help.  My final product is attached.  (At least I think it is the final product.
2011-ESF-6-Staff-Roster-PWSheet.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 35435047
Try the following experiment:
1) Launch Excel
2) Open your workbook
3) Enter password abcd in response to the prompt
4) Save the file
5) Quit Excel. When prompted to save the file, don't do it.
6) Relaunch Excel
7) Open the workbook and don't enable macros. If macros are automatically enabled by your security setting, then hold the Shift key down when you select the workbook and click the Open button. Notice how you don't see the splash screen.
8) In Excel 2007 or later, right-click any sheet tab and choose to Unhide... the UserInfo worksheet. You now have all the info needed to unprotect any worksheet.
In Excel 2003, you use the Format...Sheet...Unhide menu item to achieve the same goal.

I hope the above experiment convinces you that you need to hide all the worksheets (except the splash screen) using the Workbook_BeforeSave sub. Furthermore, make the worksheets xlVeryHidden. That way, the user can't see them in step 8 above. In fact, the code in my sample workbook is such that the user can only see the splash screen.

Brad
0
 

Author Comment

by:John Desselle
ID: 35435099
I was all for using the BeforeSave sub, until I tried your experiment.  The final document I have did not do any of that.  I went through each of your steps and could only see the Splash Screen, and could not unhide anything if macros were not enabled.

I'm not sure how, but it seems like all these protections are in place.  The only modification I made to the attached is to password protect the code using 123456.

I even tested out if the Admin adds a sheet and corresponding line on the UserInfo sheet...  Had to close and save the document, but it allowed the protection and new sheet to be available.
0
 

Author Comment

by:John Desselle
ID: 35435105
Here is the document, sorry about that.
2011-ESF-6-Staff-Roster-PWSheet.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 35435228
You didn't say which version of Excel you are using, but I have tried my suggested experiment in both Excel 2003 and 2010 using your final document. In both cases, I was able to see all worksheets after step 7. And in each case, I was able to unhide the UserInfo worksheet in step 8 so I could see that you had changed the password on me. When I tested with Excel 2003, I used password 1234 (typical of what a user might be able to do).

Brad
0
 

Author Comment

by:John Desselle
ID: 35435250
Ok, I'm sorry if I had changed a password on you.  Been testing so many different things, hard to keep track of them.

I'm using Excel 2003.  I tried your steps in both Excel 2003 and in 2007, did not get the same results.

Either way, if it is best to move the code which is in the BeforeClose sub to the BeforeSave sub, just say the word.  I'm still learning here.
0
 

Author Comment

by:John Desselle
ID: 35435312
I got it to work as you said it did, had to use 1234 as password, since I changed it from abcd....i feel like a dummy.

I see your point now, and thanks for your patience with me.

Should I move all the code from BeforeClose to BeforeSave?

If the last file I uploaded has a password on the code it is:  123456
0
 
LVL 81

Expert Comment

by:byundt
ID: 35435425
All the required code is in the ThisWorkbook code pane of my VBA sample workbook. There are three event subs (Workbook_Open, Workbook_BeforeClose and Workbook_BeforeSave). They are pretty short. Those event subs call two other subs that do the actual work of hiding and redisplaying: SpecialSave and ShowSheets. You will need to personalize the code in these last two subs in a few places; my splash screen is called Enable Macros, for example.

Since you want to request a password when the workbook opens, put that block of code in the ShowSheets sub. That way, you can leave the three event subs just as I had them.

Brad
0
 

Author Comment

by:John Desselle
ID: 35435485
Ok, working on this.  Does it matter which code is at the top of the ShowSheets?  I'm guessing I could put mine since I need the password request.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35435532
Correct.

In your case, the user has to enter a valid password before you show anything. And if a valid password is not entered, the code should exit the sub (perhaps with a message box saying that nothing would be displayed without a valid password).

Brad
0
 

Author Comment

by:John Desselle
ID: 35435645
Thanks for all the help with this.  I was just asked to make a database to keep tabs on our software.  I won't be able to work on this Excel sheet for a while.  Well, not here at work anyway.  I may work with it a little this weekend at home.  :)

John
0
 
LVL 81

Expert Comment

by:byundt
ID: 35435954
John,
I'm just down the Gulf coast from you, and it has been my pleasure to help.

Your department is going to end up with a better tool as a result of all the time we have spent collaborating. And I believe you will learn quite a bit about Excel VBA in the process of completing it.

If you have the time over the weekend, there was a one hour webinar that I gave using that sample workbook to illustrate the points (link in http:/Q_2695967.html#a35433887 ). I hope you find it interesting.

Brad
0
 

Author Comment

by:John Desselle
ID: 35436015
Thanks again Brad.  I will come back to this document, even if just in my spare time, and get a better understanding of the differences between BeforeClose and BeforeSave.  I really do appreciate all of your help.

I will look at your webinar this weekend.  

John

btw, how did you figure out i'm down near the Gulf?  I looked at your profile and can't figure out where you are.

J - in Baton Rouge
0
 
LVL 81

Expert Comment

by:byundt
ID: 35436114
John,
One of the document properties in an Excel workbook is the name of your employer. The employer name matched the parish column I saw in your worksheet, so I was pretty sure where you were.

I'm located in Panama City in the Florida Panhandle. Not so far away.

Brad
0
 

Author Comment

by:John Desselle
ID: 35436137
hehehe, pretty slick.  Since you did use the word Parish, I'll not think you may be stalking me.  LOL  I can't stand it when people call our Parishes Counties.

Anyway, hope you have a good rest of the week.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question