Password protect excel sheets and grant different permissions to different users.

[tommy75]

Hi,

I've been looking for a solution to my problem for a few weeks now. I've allready posted my question on Ozgrid, but I'm still stuck. Hopefully there's someone here to help me out...

So here's my problem:

I have a workbook containing two worksheets that should be allways visible.
So a user logs in to the workbook and can see these two sheets and a third one that's named after the user and contains working hours of that user in person.
Each user has a login and a password and logs in when opening the workbook.

I have two questions about things I cannot get done with this workbook:

- I want all sheets to be read-only when a user logs in. A user can only see the two sheets that are always visible and a third sheet containing his working hours.
- I want all sheets (of every user) to be visible when the admin logs in. All sheets should be unlocked too.

I found an interesting thread on this matter, but the attached examples have been deleted:

https://answers.microsoft.com/en-us...min-able/d59d6804-6296-4757-ad31-ce4ee2abcc3a

Can anyone help me out on this? Thanks a lot!Tommy​

 

[Logit]

.
Here is a project I picked up somewhere that does what you are seeking EXCEPT for the read only part.

Option Explicit


Dim HFD As Integer, HFR As Integer
Dim N As Long, F As Long, Pass As String


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("SetUp").Visible = xlSheetVisible
    For N = 3 To HFR
        If ComboBox1.Value = Sheets("SetUp").Cells(15, N).Value Then
            Exit For
        End If
    Next N
    
    If TextBox1.Value = Sheets("SetUp").Cells(16, N).Value Then
    Sheets("SetUp").Visible = xlSheetVeryHidden
    MsgBox Range("SetUp!C10").Value, , Range("SetUp!C9").Value & " " & Sheets("SetUp").Cells(15, N).Value
    Unload UserForm1
    Sheets("SetUp").Visible = xlSheetVisible
    Pass = Sheets("SetUp").Range("K12").Value
    Sheets("SetUp").Visible = xlSheetVeryHidden
    
        For F = 17 To HFD
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "X" Then
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
            End If
            
            If UCase(Sheets("SetUp").Cells(F, N).Value) = "P" Then
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Visible = xlSheetVisible
                Sheets(Sheets("SetUp").Cells(F, 2).Value).Protect Password:=Pass
            End If
        Next F
        
    Else
    
        MsgBox Range("SetUp!C6").Value, , Range("SetUp!C7").Value
        TextBox1.Value = ""
        Sheets("SetUp").Visible = xlSheetVeryHidden
    End If
    
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
    Unload UserForm1
End Sub
Private Sub UserForm_Initialize()
Dim WkSht As Worksheet
Application.ScreenUpdating = False


    For Each WkSht In Worksheets
        If Not WkSht.Name = "Intro" Then WkSht.Visible = xlSheetVeryHidden
    Next WkSht
        Sheets("SetUp").Visible = xlSheetVisible
        HFD = Sheets("SetUp").Range("B65536").End(xlUp).Row
        HFR = Sheets("SetUp").Range("IV15").End(xlToLeft).Column
        UserForm1.Caption = Range("SetUp!C3").Value
        Label3.Caption = Range("SetUp!C4").Value
    For N = 3 To HFR
        With ComboBox1
            .AddItem Sheets("SetUp").Cells(15, N).Value
        End With
    Next N
    
Sheets("SetUp").Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/LZP6lEbasPEPAjjvGB9YKWZ8lnMGgtR0vHAYPVf83Pr

 

[tommy75]

Hi Logit, thanks a lot!

I've got it all working now, even the read ony part...

 

[Logit]

.
You are welcome.

For benefit of others, please post your code.