Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Locking of Cells (2003)

    Cells B2:B71 allow users to select from a validation list either "New", "6 Month Renewal", "12 Month Renewal", or "24 Month Renewal". What I would like to be able to do is have the cell in column 'C' lock when anything but "New" is selected from the list. I would then like the cell to become unlocked when the list item changes to "New" or to a blank cell.

    Any help would be much appreciated! Thanks!!

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

    Re: Conditional Locking of Cells (2003)

    First, unlock cells:
    - Select the cells you want the user to be able to change (at least those in columns B and C).
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the Locked check box.
    - Click OK.

    Next, protect the sheet:
    - Select Tools | Protection | Protect Sheet...
    - Set the options you want, then click OK.

    Finally, write VBA code in the Worksheet_Change event:
    - Right-click the sheet tab.
    - Select View Code from the popup menu.
    - Enter or copy/paste the following code into the module that appears:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Range("B:B"), Target) Is Nothing Then
    Application.EnableEvents = False
    Me.Unprotect
    For Each oCell In Intersect(Range("B:B"), Target).Cells
    oCell.Offset(0, 1).Locked = (InStr(1, oCell, "month", vbTextCompare) > 0)
    Next oCell
    Me.Protect
    Application.EnableEvents = True
    End If
    End Sub

    - If you set a password on the sheet, supply it between double quotes after both the Unprotect and the Protect statement, for example

    Me.Unprotect "TopSecret"

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Locking of Cells (2003)

    Awesome!! Works perfectly!

    Thanks as always.

Posting Permissions

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