Announcement

Collapse
No announcement yet.

Allow access to certain sheets in a workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Allow access to certain sheets in a workbook



    Hi,

    I tried the following:

    I have a workbook with A Sheet called 'Agenda' and a sheet called 'Tasks'. These two sheets are Always visible.
    Then I have a sheet per employee containing their working hours. I used VBA to make sure an employee has to log in in order to see their personal sheet and the two sheets I mentioned before.

    I have two questions about this:

    - Can I make the visible sheets after the employee logs in 'read only'?
    - Is there a way to log in as an admin and unlock all the sheets at once?

    I use the following code for the workbook:

    Code:
    rivate Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim w As Worksheet
        Dim bSaveIt As Boolean
    
        bSaveIt = False
        For Each w In Worksheets
            If w.Visible Then
                Select Case w.Name
                    Case "employee1"
                        w.Protect ("paswoord1")
                        w.Visible = False
                        bSaveIt = True
                    Case "employee2"
                        w.Protect ("paswoord2")
                        w.Visible = False
                        bSaveIt = True
                    Case "employee3"
                        w.Protect ("paswoord3")
                        w.Visible = False
                        bSaveIt = True
                    Case "employee4"
                        w.Protect ("paswoord4")
                        w.Visible = False
                        bSaveIt = True
                    Case "employee5"
                        w.Protect ("paswoord5")
                        w.Visible = False
                        bSaveIt = True
                    Case "employee6"
                        w.Protect ("paswoord6")
                        w.Visible = False
                        bSaveIt = True
                   
                End Select
            End If
        Next w
        If bSaveIt Then
            ActiveWorkbook.CustomDocumentProperties("auth").Delete
            ActiveWorkbook.Save
        End If
    End Sub
    
    Private Sub Workbook_Open()
        UserForm1.Show
    End Sub
    
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        If Sh.Name <> "Agenda" Then
        If Sh.Name <> "Tasks" Then
            If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then
                Sh.Visible = False
                MsgBox "You have no right to view this sheet"
            End If
        End If
        End If
    End Sub
    I use the following code in the login form:

    Code:
    Dim bOK2Use As Boolean
    
    Private Sub btnOK_Click()
        Dim bError As Boolean
        Dim sSName As String
        Dim p As DocumentProperty
        Dim bSetIt As Boolean
    
        bOK2Use = False
        bError = True
        If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
            bError = False
            Select Case txtUser.Text
                Case "Annemie"
                    sSName = "Employee1"
                    If txtPass.Text <> "paswoord1" Then bError = True
                Case "Anouk"
                    sSName = "Employee2"
                    If txtPass.Text <> "paswoord2" Then bError = True
                Case "Bellinda"
                    sSName = "Employee3"
                    If txtPass.Text <> "paswoord3" Then bError = True
                Case "Ingeborg"
                    sSName = "Employee4"
                    If txtPass.Text <> "paswoord4" Then bError = True
                Case "Nina"
                    sSName = "Employee5"
                    If txtPass.Text <> "paswoord5" Then bError = True
                Case "Yvonne"
                    sSName = "Employee6"
                    If txtPass.Text <> "paswoord6" Then bError = True
                Case Else
                    bError = True
            End Select
        End If
        If bError Then
            MsgBox "Not a valid username or password"
        Else
            'Set document property
            bSetIt = False
            For Each p In ActiveWorkbook.CustomDocumentProperties
                If p.Name = "auth" Then
                    p.Value = sSName
                    bSetIt = True
                    Exit For
                End If
            Next p
            If Not bSetIt Then
                ActiveWorkbook.CustomDocumentProperties.Add _
                  Name:="auth", LinkToContent:=False, _
                  Type:=msoPropertyTypeString, Value:=sSName
            End If
    
            Sheets(sSName).Visible = True
            Sheets(sSName).Unprotect (txtPass.Text)
            Sheets(sSName).Activate
    
            bOK2Use = True
            Unload UserForm1
           
           
        End If
    End Sub
    
    Private Sub UserForm_Terminate()
        If Not bOK2Use Then
            ActiveWorkbook.Close (False)
        End If
    End Sub
    Anyone an idea?

    Thanks in advance!

    Tomski

  • #2
    No one an idea?

    Comment


    • #3
      .
      Repeat the portion of your macro that writes the data to the "Agenda" sheet except specify it be written to the "Tasks" sheet instead.

      Comment


      • #4


        Logit, I'm afraid I don't understand what you're saying :-)
        How does that answer the two questions I asked?


        - Can I make the visible sheets after the employee logs in 'read only'?
        - Is there a way to log in as an admin and unlock all the sheets at once?


        Comment

        Working...
        X