PassWord Protected Worksheet for End User
Posted by Jim Spencer on January 02, 2001 7:15 AM
I know this is simple for you Excel Experts, but I need it explained in plain English
Excel Worksheet has different tabs for Different departments..
Sheet1=DeptA, Sheet2= Dept B Tab Sheet3 = Dept C Tab etc..
I need a Visual Basic routine or Macro that when the Dept A manager clicks on the Sheet1, It prompts him to enter a Password before he/she can view the data. The Data is protected by the password before the End-user can view any of the data on Sheet1 Tab, etc.
Because of the sensitivity of Data only Dept A Manager can see Dept A data. If Dept A Manager clicks on Sheet2 for Dept B data, he/she would have to know Dept B manager's Password to access.
The clincher is the Director of Dept A B and C (sheet1,2,3,)should be allowed access to all three tabs. Therefore a highlevel password would allow the Director to enter his password to be able to access all 3 tabs for viewing/changing as needed.
Can you help...
Thank you.
Jim Spencer
Posted by Dave on January 02, 2001 7:54 AM
Hi Jim
Lets see if I understand you correctly.
If you only have these 3 sheets then insert another blank sheet (reason is one sheet must always be visible in Excel). Then right click on the sheet picture (top left next to "File") and select "View Code". Paste this code over the top:
Private Sub Workbook_Open()
Sheet1.Visible = xlVeryHidden
Sheet2.Visible = xlVeryHidden
Sheet3.Visible = xlVeryHidden
End Sub
I have used the sheets "Code names" as this will allow for anyone changing the sheet names. You can see the sheet code names in the Project Explorer to the left. If you can't then Push ctrl+R.
Now go to Insert>Module and paste in this code:
Sub Viewer()
Dim Reply As String
Reply = InputBox("Please enter your password", "Sheet Viewer")
Select Case Reply
Case "Secret1"
Sheet1.Visible = xlSheetVisible
Case "Secret2"
Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVisible
Case "Secret3"
Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVisible
Sheet3.Visible = xlSheetVisible
Case Else
Exit Sub
End Select
End Sub
This will display a dialog box for the users to insert their CASE SENSITIVE password. The sheets displayed depends on the password entered. I have used "Secret1" etc but of course you will change these.
Now go to Tools>VBA Project-Poject Poperties and click Protection, check the "Lock project for viewing" box and enter a password twice. Write it Down!!!
Push Alt+Q and then Alt+F8, click "Viewer" then Options and set a shortcut key. then Ok and Ok.
Now save and close.
Now reopen your Workbook and all 3 sheets should be veryhidden (which means they can only be unhidden via VBA). You can apply a password to each sheet if needed. But again write it down!!
You should also be aware that Excel is not totaly secure and should not be used for information that is extremely sensitive.
Any Good ?
OzGrid Business Applications
Posted by Jim on January 02, 2001 3:09 PM
Posted by Jim on January 02, 2001 3:15 PM
Thanks Dave,,,
That code set me off on the Right Foot...
I may have more questions later..but for now,I am able to click on the Command Button and it asked for the password and after the password is entered correctly it does Sheet1.Select and Activates Sheet1 at Cell Address A1.
Have a Happy New Year and thanks again for the Code and information.
Jim
Posted by Dave on January 03, 2001 1:35 AM
That code set me off on the Right Foot... I may have more questions later..but for now,I am able to click on the Command Button and it asked for the password and after the password is entered correctly it does Sheet1.Select and Activates Sheet1 at Cell Address A1.
No trouble at all Jim. Happy new year to you too!
- OzGrid Business Applications
Posted by Mary on April 22, 2001 9:09 AM
Posted by Mary on April 22, 2001 9:12 AM
This code is very helpful. As an infant in VBA
how would I go about setting the code such that on opening the workbook A,B,or C are asked to submit a password (instead of using control + key?
