Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Locking a Cell (2003)

    Loungers,

    I have a spread sheet where a person enters a date that a certain task will be completed.

    Is there a way to lock this particular cell after a date has been entered to prevent it being changed to another date at another time - could perhaps require a password or something

    Any thoughts?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locking a Cell (2003)

    See the attached workbook. The worksheet is protected (without a password for simplicity, but you can add one if desired), and the cells in colmns A and B are unlocked. If you enter a value in column A, the cell is locked by the Worksheet_Change event.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect ' "Secret"
    For Each oCell In Intersect(Target, Range("A:A")).Cells
    If Not oCell = "" Then
    oCell.Locked = True
    End If
    Next oCell
    Me.Protect ' "Secret"
    Application.EnableEvents = True
    End If
    End Sub

    Warning: this will only work if the user enables macros.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Locking a Cell (2003)

    Hans

    Perfect - as usual!!

    Thanks for the quick response

Posting Permissions

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