Require password to view worksheet

[WeThotUWuzAToad]

Hello,


How do you protect a given worksheet so that a password is required even to view the worksheet?


For example, suppose you have a workbook with four worksheets and you want the viewing of any given worksheet to require entry of a corresponding password (or a master password) as shown in the screenshot located here:

https://imgur.com/ceEayAx

I've googled this question but the results tend to address protection relating to change privileges rather than viewing privileges.


Thanks

 

[jproffer]

One way would be to put this in the Workbook code module:

Private Sub Workbook_Open()
Sheet1.Visible = xlSheetVeryHidden
End Sub

and in a standard module, put this:

Sub Password()
Dim Ans As String

Ans = InputBox("Please enter the password to view Sheet1")

If Ans = "CorrectPassword" Then Sheet1.Visible = xlSheetVisible

End Sub

If you wanted to you could make it VeryHidden on the Workbook_SheetChange event, so every time they clicked off that worksheet to a different one, it would become invisible and require a password again to view.

 

[Logit]

.
Here is a SELECT CASE macro to make it easier to separate the password for each sheet :

Option Explicit


Sub chkMacroaccess()
Dim strName As String


    strName = InputBox(Prompt:="Enter password please.", _
          Title:="ENTER YOUR PASSWORD")
        
        If strName = vbNullString Then
           


           Application.DisplayAlerts = False
           ThisWorkbook.Saved = True
           Application.Quit


        Else


          Select Case strName
           
            Case "krunk"
                MsgBox "This would be accessing your Macro [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] " 'unhiding Sheet 2
            Case "logit"
                MsgBox "This would be accessing your Macro [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] " 'unhiding Sheet 3
            Case "all"
                MsgBox "This would be accessing your Macro [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] " 'unhiding all Sheets
            Case Else
                MsgBox "Sorry, wrong password."
                Exit Sub
          End Select


        End If


End Sub

Download Workbook : https://www.amazon.com/clouddrive/share/BLRugMe4Z5PycE7c3q9mAVPDn5k5a5y9s015n1feeHC

 

[WeThotUWuzAToad]

@jproffer


Thank you for the response.

One way would be to put this in the Workbook code module...

I should have mentioned in the OP that I have no experience using VBA. About all I know how to do is open the Visual Basic window from the Ribbon Developer tab.


I pasted both of your codes but I'm not sure if they are in the correct locations (ie modules) so here is a screenshot:


https://imgur.com/lR0dcWk


Also, I don't know what to do next in terms of creating usernames and/or passwords for accessing specific worksheets.


Thanks

 

[WeThotUWuzAToad]

@Logit


Thank you for the response and for the sample workbook.

.
Here is a SELECT CASE macro to make it easier to separate the password for each sheet...

I like the simplicity of clicking once to bring up the password box and then entering a password to run a defined macro. That obviously opens up many more options than simply accessing a particular worksheet.


However, as mentioned above, I don't know how to write the VBA code to create the macro(s). (By the way, I do use Excel frequently and I am quite comfortable using Excel functions but it is infrequent that I have need for VBA so that's why I have never learned it.)


I sense that I should have included more specific details in my OP but I didn't so here it is now:


My aim with this is to create confidential (password-protected) sign-in/sign-out timesheets for my half dozen or so employees. I've got the form I want them to use in an Excel workbook on a laptop located in the their work area but I want them to have access only to their own timesheets and not the timesheets of other employees.


The workbook I have in mind contains the following worksheets (with worksheet names shown in brackets):


[SplashPage] default page to display when workbook is opened; includes a "Sign in" button to open the Password box
[EmployeeList] contains a chart showing employees' names, passwords, phone numbers, etc.
[CombinedData] uses Excel formulas to summarize combined sign-in/sign-out data from employee worksheets
[Bob] timesheet for Bob only
[Carol] timesheet for Carol only
[Ted] timesheet for Ted only
[Alice] timesheet for Alice only
[Timesheet template] to create timesheets for new employees


Notes


1) Each employee has access only to his/her respective worksheet.


2) I need some way to unhide and access all worksheet tabs when I need to.


3) The workbook does not need a pop-up box for employees to create/change their passwords since I can insert them manually into the code if I know where/how to do that.


4) Employees do not need usernames (ie the simple password box in your attached workbook is perfect).


5) Employees do not have access to the VBA code


Sorry for the lengthy post but hopefully too much information is better than too little.


Thanks

 

[Logit]

.
Ok .... it is alot easier knowing what your layout is and the final goal. It is late here now (almost midnight). Give me until tomorrow morning to put something together for you.

 

[Logit]

.
This macro is pasted in ThisWorkbook module :

Option Explicit




Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    Dim wsName As String
    wsName = "Splash Page"
    
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> wsName Then
            ws.Visible = xlSheetHidden
        End If
    Next
    
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    
End Sub

This goes in a Routine Module :

Option Explicit


Sub chkMacroaccess()
Dim strName As String
On Error GoTo errHandler:
    strName = InputBox(Prompt:="Enter password please.", _
          Title:="ENTER YOUR PASSWORD")
        
        If strName = vbNullString Then
 
          
           Application.DisplayAlerts = False
           ThisWorkbook.Saved = True
           Application.Quit


        Else


          Select Case strName
           
            Case "krunk"
                Sheets("Bob").Visible = xlSheetVisible
                
            Case "logit"
                Sheets("Carol").Visible = xlSheetVisible
                
            Case "pass"
                Sheets("Ted").Visible = xlSheetVisible
                
            Case "go"
                Sheets("Alice").Visible = xlSheetVisible
                
            Case "all"
                Dim ws As Worksheet
                Dim wsName As String
                wsName = "Splash Page"
                For Each ws In Application.ActiveWorkbook.Worksheets
                    If ws.Name <> wsName Then
                        ws.Visible = xlSheetVisible
                    End If
                Next
                
            Case Else
                MsgBox "Sorry, wrong password."
                Exit Sub
                
          End Select


        End If
    Exit Sub
'Error block
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
    & Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub

Download file : https://www.amazon.com/clouddrive/share/RnLvjJS1mvNN9zx9MbS2KeSLrQvD6jFAyyD7sfhBnQt

 

[WeThotUWuzAToad]

Thanks a bunch Logit. This is exactly what I was after.


One thing I noticed is that changes seem to be saved automatically rather than by getting a pop-up box with an optional Save when you exit. Is that by design or something associated with the VBA code?


Also, I added a worksheet named Mike then inserted the following into the code:


Case "word"
Sheets("Mike").Visible = xlSheetVisible


That seemed to do the trick but is there anything else I should be aware of when adding additional users?

Finally, is there a way to password-protect the VBA code? I noticed that currently, it opens right up by going to:


Developer tab > Visual Basic


I realize the Developer tab can be hidden but Excel-savvy users will know how to access it.

 

[Logit]

.

One thing I noticed is that changes seem to be saved automatically rather than by getting a pop-up box with an optional Save when you exit. Is that by design or something associated with the VBA code?

These two lines in the ThisWorkbook_Close event doing the auto-saving :

Application.DisplayAlerts = False
    ActiveWorkbook.Save

If you want the see the "Save Changes ?" message, just comment out those two lines.

Also, I added a worksheet named Mike then inserted the following into the code:


Case "word"
Sheets("Mike").Visible = xlSheetVisible


That seemed to do the trick but is there anything else I should be aware of when adding additional users?

You should be good to go with that as adding additional users. If you add ALOT of users it might be better to approach
the project with something different. Otherwise you'll end up with a long list of SELECT CASE calls.

Finally, is there a way to password-protect the VBA code? I noticed that currently, it opens right up by going to:
Developer tab > Visual Basic
I realize the Developer tab can be hidden but Excel-savvy users will know how to access it.

Well, the problem with Excel is that is was designed with almost no security. This has been a thorn in people's side since Excel was created.
There have been many attempts by different people and companies to make it secure, but there has always been a hack or means to circumvent the
protection. Even the companies that sell you a software package costing hundreds of dollars. There is a way around it if you know what you are doing.

You can password protect the VBA code in the VB Editor. Instructions : http://www.ozgrid.com/VBA/protect-vba-code.htm
This will only keep out the non-savvy Excel user. If a person understand VBA and has done some searching on the internet they will find instructions how
to break your password.

 

[WeThotUWuzAToad]

By the way, can you also tell me how to designate a different graphic (object) to use for opening the Password box?


For example, if I created an object in PowerPoint (eg say it's a green rectangle with rounded corners and the words "Sign In") then pasted it to the Splash Page, could I use that in place of the current gray rectangle with the words, "Access Macro"?

Thanks!