Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Auto Lock & Protect Cells After Entry

  1. #1
    Join Date
    16th June 2004
    Location
    Canada Quebec
    Posts
    260

    Auto Lock & Protect Cells After Entry

    Hi

    How would I go about lock a cell after there is an entry. Once the entry has been made the user could not change the entry. For example if a user enter in cell b2 he could not change the entry, he could change a2, c2, d2. He would also be able to put an other entry in b3, once enter the cell is lock.

    I hope it is clear

    Thanks
    Denis

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd October 2006
    Location
    Cardiff, UK
    Posts
    263

    Re: Lock Cell After Entry

    Well, if the workbook is protected then you can lock cells by using

    VB:
    Function InRange(Range1 As Range, Range2 As Range) As Boolean 
         'returns True if Range1 is within Range2
        Dim InterSectRange As Range 
        Set InterSectRange = Application.Intersect(Range1, Range2) 
        InRange = Not InterSectRange Is Nothing 
        Set InterSectRange = Nothing 
    End Function 
    
    
    This allows you to specify a range in the below.

    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If InRange(ActiveCell, Range("B1:B100")) Then 
            ActiveSheet.Unprotect 
            ActiveCell.Offset(-1, 0).Locked = True 
            ActiveSheet.Protect 
        Else: End If 
        End Sub 
    
    
    This will mean that if you enter something in B7 and press enter (and you have enter to drop down to next cell in column (default setting in Excel)) then cell B& will then have the attribute 'locked'.

    If you're not protecting the workbook then it's likely a lot more complicated (certainly i'm stumped).

    HTH

    KJ
    Last edited by Kneejerk; March 1st, 2007 at 00:03.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,429

    Re: Lock Cell After Entry

    Not quite sure which cells, but the sheet will need protecting

    VB:
    Option Explicit 
     
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        If Target.Column > 2 Then Exit Sub 'only works in Columns A & B
        With ActiveSheet 
            .Unprotect 
            If Target.Value > "" Then Target.Locked = True 
            .Protect 
        End With 
    End Sub 
    
    
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    16th June 2004
    Location
    Canada Quebec
    Posts
    260

    Re: Lock Cell After Entry

    Thanks Kneejerk very helpfull

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    3rd October 2006
    Location
    Cardiff, UK
    Posts
    263

    Re: Lock Cell After Entry

    I think royUK's would likely work better and be more error free, but glad to have helped.

    KJ

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th June 2004
    Location
    Canada Quebec
    Posts
    260

    Re: Lock Cell After Entry

    Thanks Roy very helpfull

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    12th April 2011
    Posts
    14

    Re: Lock Cell After Entry

    I am also facing the same issue, attaching the file please use the same in this fileTest.xls

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Auto Lock & Protect Cells After Entry

    Please start your own thread, thanks.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Protect / Lock Cells Based On Color
    By Kipo in forum EXCEL HELP
    Replies: 4
    Last Post: May 28th, 2013, 16:13
  2. Replies: 3
    Last Post: May 1st, 2008, 04:33
  3. Protect/Lock Sheets/Cells But Allow Macro Code
    By aws_fahed in forum EXCEL HELP
    Replies: 1
    Last Post: April 30th, 2008, 00:44
  4. Lock/Protect Select Cells Only
    By ScottMc in forum EXCEL HELP
    Replies: 2
    Last Post: April 18th, 2008, 12:35
  5. Automatically Lock & Protect Cells
    By nigebarrett in forum EXCEL HELP
    Replies: 11
    Last Post: December 2nd, 2006, 05:01

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
  •  
porno