Results 1 to 8 of 8

Need help with a particular code - Unlock/Unprotect sheets with different password

Threaded View

mark182005 Need help with a particular... 02-24-2015, 08:42 AM
HaHoBe Re: Need help with a... 02-24-2015, 10:04 AM
mark182005 Re: Need help with a... 02-24-2015, 11:22 AM
HaHoBe Re: Need help with a... 02-24-2015, 11:46 AM
mark182005 Re: Need help with a... 02-24-2015, 01:22 PM
mark182005 Re: Need help with a... 02-24-2015, 01:36 PM
mark182005 Re: Need help with a... 02-24-2015, 03:28 PM
mark182005 Re: Need help with a... 02-25-2015, 09:16 AM
  1. #1
    Registered User
    Join Date
    03-26-2014
    Location
    Gatineau, Qc
    MS-Off Ver
    Excel 2007
    Posts
    12

    Need help with a particular code - Unlock/Unprotect sheets with different password

    Hi everyone,

    here is a code that when opening the sheet and enabling macros, prompts you to enter a username and password. It then unlocks a certain sheet according to the username/password combination. I would like to add a code that would unlock all sheets of the workbook with the entry of an Admin account/password.

    Thanks for your help

    Workbook Code:

    Private 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 "Anie Arsenault"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Catherine Berube"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Gail Farmer"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Susan Flynn"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Laura Forsythe"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Elise Gauthier"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Philippe Gauthier"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Shirley Lavergne"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Aika Mistiva"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Manon St-Louis"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Celine Traversy"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Jean-Francois Boos"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Tyler Declare"
                        w.Protect ("password")
                        w.Visible = False
                        bSaveIt = True
                    Case "Jessica Kairouz"
                        w.Protect ("password")
                        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 <> "Monthly Stats" Then
            If Sh.Name <> ActiveWorkbook.CustomDocumentProperties("auth").Value Then
                Sh.Visible = False
                MsgBox "Vous n'avez pas l'autorisation de voir cette page / You don't have authorization to view that sheet!"
            End If
        End If
    End Sub

    Userform 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 "aarsenau"
                    sSName = "Anie Arsenault"
                    If txtPass.Text <> "password" Then bError = True
                Case "gfarmer"
                    sSName = "Gail Farmer"
                    If txtPass.Text <> "password" Then bError = True
                Case "Sflynn"
                    sSName = "Susan Flynn"
                    If txtPass.Text <> "password" Then bError = True
                Case "lforsyth"
                    sSName = "Laura Forsythe"
                    If txtPass.Text <> "password" Then bError = True
                Case "egauthie"
                    sSName = "Elise Gauthier"
                    If txtPass.Text <> "password" Then bError = True
                Case "pgauthie"
                    sSName = "Philippe Gauthier"
                    If txtPass.Text <> "password" Then bError = True
                Case "slavergn"
                    sSName = "Shirley Lavergne"
                    If txtPass.Text <> "password" Then bError = True
                Case "amistiva"
                    sSName = "Aika Mistiva"
                    If txtPass.Text <> "password" Then bError = True
                Case "mstlouis"
                    sSName = "Manon St-Louis"
                    If txtPass.Text <> "password" Then bError = True
                Case "ctravers"
                    sSName = "Celine Traversy"
                    If txtPass.Text <> "password" Then bError = True
                Case "jboos"
                    sSName = "Jean-Francois Boos"
                    If txtPass.Text <> "password" Then bError = True
                Case "tdeclare"
                    sSName = "Tyler Declare"
                    If txtPass.Text <> "password" Then bError = True
                Case "jkairouz"
                    sSName = "Jessica Kairouz"
                    If txtPass.Text <> "password" Then bError = True
                Case "cberube"
                    sSName = "Catherine Berube"
                    If txtPass.Text <> "password" Then bError = True
                Case Else
                    bError = True
            End Select
        End If
        If bError Then
            MsgBox "Invalid User Name 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
    Last edited by mark182005; 02-24-2015 at 02:13 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to Unprotect sheets-unlock range of cells-protect sheets
    By jrace in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-09-2013, 10:45 AM
  2. [SOLVED] Ran Macro that Password Protected Sheets, Now won't Unprotect
    By pjdumont in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-06-2013, 02:17 AM
  3. [SOLVED] Password protect and unprotect all sheets EXCEPT two named sheets
    By Ducatisto in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-29-2012, 05:43 AM
  4. Macros to protect and unprotect sheets (Without a password)
    By David_S_Walker in forum Excel General
    Replies: 6
    Last Post: 03-30-2010, 06:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1