Results 1 to 2 of 2

Macro to lock the worksheet using an embedded password?

Threaded View

  1. #1
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Macro to lock the worksheet using an embedded password?

    The code below controls two macros. The first one locks the worksheet, the other unlocks the worksheet; and both require that you enter a password to lock and/or unlock it. I would like to modify the code below so that it ONLY requires you to enter a password to unlock it. When users push the button to lock the worksheet, I do not want them to have to enter a password, as they are not supposed to know the password. Instead it should just automatically lock the the worksheet based on something in the code that knows the password is "1234".

    Const pw As String = "1234"
    
    Sub lockworksheet1() '
    ' lockworksheet1 Macro'
    
    Dim pw     As String
        pw = Application.InputBox("Enter password")
        Select Case pw
            Case "1234"
                'run code
            Case Else
                Exit Sub
        End Select
    
    ActiveSheet.Unprotect pw
    
    Range("B4:O30").Select
        Selection.Locked = True
        Selection.FormulaHidden = True
        Range("A1:O1").Select
        
        Range("B4:O29").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -9.99786370433668E-02
            .PatternTintAndShade = 0
        End With
        Range("A1:O1").Select
    
      ActiveSheet.Protect pw
      
    End Sub
    
    
    Sub unlockworksheet1() '
    ' unlockworksheet1 Macro'
    
    Dim pw     As String
        pw = Application.InputBox("Enter password")
        Select Case pw
            Case "1234"
                'run code
            Case Else
                Exit Sub
        End Select
    
      ActiveSheet.Unprotect pw
      
      Range("B5:O14").Select
        Selection.Locked = False
        Selection.FormulaHidden = True
        Range("B16:O16").Select
        Selection.Locked = False
        Selection.FormulaHidden = True
        Range("B18:O27").Select
        Selection.Locked = False
        Selection.FormulaHidden = True
        Range("B29:O29").Select
        Selection.Locked = False
        Selection.FormulaHidden = True
        Range("B30:K30").Select
        Selection.Locked = False
        Selection.FormulaHidden = True
        Range("N30:O30").Select
        Selection.Locked = False
        Selection.FormulaHidden = True
        
        Range("B5:O14").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("B16:O16").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("B18:O27").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("B29:O29").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("A1:O1").Select
        
        ActiveSheet.Protect pw
      
    End Sub
    Last edited by jonvanwyk; 06-18-2012 at 01:50 PM. Reason: clarity

Thread Information

Users Browsing this Thread

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

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