Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    unlock cell on condition (XP)

    All cells in column E of my sheet are locked. However if a user enters a certain value in cell say D5, I would like cell E5 to become unlocked so that the data can be overwritten. This need to apply for any row.

    What code do I need and what event should trigger the unlocking?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: unlock cell on condition (XP)

    You should use the Change event in the worksheet's code module:

    This unlocks any cell in column E when the cell to the immediate left of it is changed.

    Rightclick the sheet's TAB and choose View code. Paste this into the code window you get:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("D")) Is Nothing Then Exit Sub
    Me.Protect userinterfaceonly:=True
    Target.Offset(, 1).Locked = False
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: unlock cell on condition (XP)

    That will work for ANY value entered.
    For 'certain' values you would need to check what was actually entered.
    Maybe another hidden 'check' column could be used containing the 'allowed' values?

    zeddy

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

    Re: unlock cell on condition (XP)

    You can check for allowed values in the Worksheet_Change event itself. The Target argument is a Range object that represents the cell that has changed. You can inspect its value, and only unlock the cell to the right if the value meets certain conditions.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: unlock cell on condition (XP)

    That's what I meant.
    But instead of 'hard-coding' the required values in VBA fetch the appropriate values from an offset value from the active cell.

    zeddy

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

    Re: unlock cell on condition (XP)

    OK, that's a good way to do it if the conditions are not fixed.

Posting Permissions

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