+ Reply to Thread
Results 1 to 3 of 3

How to lock and unlock worksheet using a password but inside worksheet change event

Hybrid View

  1. #1
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    403

    How to lock and unlock worksheet using a password but inside worksheet change event

    Hi everyone,

    The code below works perfectly as long as the sheet is unlocked when the watched cell changes. By working perfectly, I mean that when the data in the current row column C changes from blank to any data entered, the current static date and time is entered into a cell 26 columns away from cell C in column AC. The problem is that I have been completely unsuccessful using sheet locking and unlocking code in this worksheet change event like I use it within macros. I need for it to unlock the sheet with a password I specify, apply the change and then lock the sheet again and setting the same specified password.

    Option Explicit
    ' *********************************************************
    ' Create a date/time stamp
    ' ********************************************************
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorEvent
    
    ' *********************************************************
    ' Only Allow A Single Cell Range
    ' *********************************************************
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If
    
    ' ********************************************************
    ' Turn Off EnableEvents To Prevent Looping
    ' ********************************************************
    Application.EnableEvents = False
    
    ' ********************************************************
    ' Apply the Change If Data is Entered in Current Row Column C
    ' ********************************************************
    If Not Application.Intersect(Target, Columns("C")) Is Nothing Then
        Cells(Target.Row, Target.Column + 26) = Now()
    End If
    
    ExitNormally:
    ' ********************************************************
    ' Return EnableEvents to Normal Mode
    ' ********************************************************
        Application.EnableEvents = True
        Exit Sub
    ErrorEvent:
        MsgBox Err.Description
        Resume ExitNormally
    End Sub
    I've been trying to use the following to unlock and lock but it doesn't work in the change event.

    ' Unlock the sheet
        Set wsh = Worksheets("Youth")
         On Error Resume Next
        wsh.Unprotect Password:="Rcdc"
    '
    ' Lock the sheet
        wsh.Protect Password:="Rcdc"
    I need some words of VBA guru wisdom. Can you help?
    -------------
    Tony

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: How to lock and unlock worksheet using a password but inside worksheet change event

    Hi, try this (untested):

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ErrorEvent
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect Password:="Rcdc"
    
    If Not Application.Intersect(Target, Columns("C")) Is Nothing Then
        Cells(Target.Row, Target.Column + 26) = Now()
    End If
    
    ExitNormally:
        Application.EnableEvents = True
        ActiveSheet.Protect Password:="Rcdc"
        Exit Sub
    ErrorEvent:
        MsgBox Err.Description
        Resume ExitNormally
    
    End Sub

  3. #3
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    403

    Re: How to lock and unlock worksheet using a password but inside worksheet change event

    Berlan, I can't thank you enough! It works exactly as I need it to.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Use Cell Value for password to unlock worksheet
    By BKREMER in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-29-2013, 05:24 PM
  2. Lock and unlock sheets with password , diffrent usernames different actions
    By Barry Engelbrecht in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2013, 07:06 AM
  3. [SOLVED] Macro to lock the worksheet using an embedded password?
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2012, 01:50 PM
  4. worksheet lock / unlock problem
    By xes in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2010, 11:14 AM
  5. Replies: 5
    Last Post: 06-23-2005, 06:05 PM

Tags for this Thread

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