CODEWITHSUNDEEP
excelvba
Stiley
Stiley

Reputation: 131

Excel - Limit view of worksheets to certian users

I have an excel workbook containing several worksheets.

What I want to do is have a mechanism like a user form or something where the user would authenticate to one of several possible users.

Based on the username supplied I want to display certain worksheets and hide other sheets, and block the user from accessing worksheets they should not be able to view.

Has anyone done something like this in Excel?

Any thoughts are appreciated Sean

Upvotes: 1

Views: 131

Answers (2)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

I actually enjoyed the task of typing this one up. Keep in mind, VBE is not protected in this code so you may want to add some protection, but this should do what you need.

You should also create a generic Login worksheet. This would be the only sheet open before a password is entered. This is essential as you are unable to hide every sheet without throwing an error. (You need to have 1 visible sheet).

WARNING: This code is mildly tested. You are responsible for any loss of data for using the below code, such as (but not limited to) forgetting a password. You have been warned!!!!

1. Open the Workbook, then make a Call to GetLogin

Option Explicit

Private Sub Workbook_Open()

    GetLogin 1

End Sub

2. The Login Code

Private Sub GetLogin(ByVal AttemptNumber As Integer)

    Dim Sheet As Worksheet

    With ThisWorkbook.Worksheets("Login")
        .Visible = xlSheetVisible
        .Activate
    End With

    For Each Sheet In ThisWorkbook.Sheets
        If Not Sheet.Name = "Login" Then
            Sheet.Visible = xlSheetVeryHidden
        End If
    Next Sheet

    Dim Password As String
    Password = Application.InputBox("Please enter your password")

    Select Case Password
        Case "Ma$terPas$"
            For Each Sheet In ThisWorkbook.Sheets
                Sheet.Visible = xlSheetVisible
            Next Sheet
            ThisWorkbook.Worksheets(1).Activate 'For when you hide login sheet
        Case "Oth3Rpa$$"
            With ThisWorkbook
                .Worksheets(1).Visible = xlSheetVisible
            End With
            ThisWorkbook.Worksheets(1).Activate 'For when you hide login sheet
        Case Else
            If AttemptNumber <= 3 Then
                If MsgBox("You entered an incorrect password", vbRetryCancel, "Attempt # " & AttemptNumber) = vbRetry Then
                    AttemptNumber = AttemptNumber + 1
                    GetLogin AttemptNumber
                Else
                    ThisWorkbook.Saved = True
                    ThisWorkbook.Close
                End If
            Else
                ThisWorkbook.Saved = True
                ThisWorkbook.Close
            End If
    End Select

    ThisWorkbook.Worksheets("Login").Visible = xlSheetHidden

End Sub

3. Close the Workbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If ThisWorkbook.Saved = False Then
        If MsgBox("Would you like to save?", vbYesNo) = vbYes Then
            ThisWorkbook.Save
        End If
    End If

    Dim Sheet As Worksheet
    With ThisWorkbook.Worksheets("Login")
        .Visible = xlSheetVisible
        .Activate
    End With

    For Each Sheet In ThisWorkbook.Sheets
        If Not Sheet.Name = "Login" Then
            Sheet.Visible = xlSheetVeryHidden
        End If
    Next Sheet

    'Prevent from being asked to save the fact you just hid the sheets
    ThisWorkbook.Saved = True

End Sub

Ensure that the Workbook_Open and Workbook_Close are in your Workbook's Module.

Upvotes: 2

Marcucciboy2
Marcucciboy2

Reputation: 3263

You could probably achieve this by using the Auto_Open event

Function Auto_Open()
    Select Case True
    Case InStr(Application.UserName, "Dan Smith") > 0
        ActiveWorkbook.Sheets(1).Visible = xlSheetVeryHidden
    Case InStr(Application.UserName, "Jon Doe") > 0
        ActiveWorkbook.Sheets(1).Visible = True
    End Select
End Function

Of course this would take a lot of work considering you'd have to find out everyone's usernames and then the sheets that you want to hide from them, but that's what I thought of

Upvotes: 2

Related Questions