Announcement

Collapse
No announcement yet.

Auto Lock & Protect Cells After Entry

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Lock Cell After Entry

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

    Code:
    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.

    Code:
    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, 00:03.

    Comment


    • #3
      Re: Lock Cell After Entry

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

      Code:
      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

      New users should read the Forum Rules before posting

      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.

      Comment


      • #4
        Re: Lock Cell After Entry

        Thanks Kneejerk very helpfull

        Comment


        • #5
          Re: Lock Cell After Entry

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

          KJ

          Comment


          • #6
            Re: Lock Cell After Entry

            Thanks Roy very helpfull

            Comment


            • #7
              Re: Lock Cell After Entry

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

              Comment


              • #8


                Re: Auto Lock & Protect Cells After Entry

                Please start your own thread, thanks.

                Comment

                Working...
                X